Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Help showing results

Discussion in 'Information Technology' started by Jerid B, Jul 28, 2006.

  1. Jerid B

    Jerid B
    Expand Collapse
    Guest

    I will try and sum up the best I can. Access 2003.
    I created a "Request for Quote" database. It's rather simple.
    2 Tables.
    Table 1 has a place for quote number, quantity, description, and date. Also
    has a spot to fill in for 4 vendors because we send this out to 4 different
    vendors at a time. So there's field Vendor 1, Vendor 2, Vendor 3, Vendor 4.
    Table 2 has Vendor info. Name, address, city, zip, etc...
    So I create a form and put in all the data. I am using a combo box to call
    the vendor table for Vendor input. Right now it shows Ven name, address, and
    etc.., but only Vendor name is stored in that field.
    All is well.
    So now I want a report to show me everything filled out on the form, but I
    want the addresses and tele #'s of the vendors to show up.
    I can't get the report to show me the details of the vendor.
    If I run a query I have to link Vendor 1 to the Ven table and I get Ven1's
    info.
    So I find out if I run 4 query's and change the link (relationship) I can
    have Query Ven 1, Query Ven 2, etc...
    So if I could run a query to run the 4 queries it would work, but it seems I
    have made things more complicated than it probably has to be.
    I am looking for another idea to try or way to link these tables together.
    I've been racking my brain for days and just can't figure this one out.
    HELP!
     
  2. Loading...


  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    The appropriate thing to do would be to have a second table that has one row
    for each of the 4 vendors, linked to your quote table. Then, you can join
    the 3 tables together and get all of the information you need.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    news:A941E262-737F-41E5-949F-627523E5C0D1@microsoft.com...
    > I will try and sum up the best I can. Access 2003.
    > I created a "Request for Quote" database. It's rather simple.
    > 2 Tables.
    > Table 1 has a place for quote number, quantity, description, and date.

    Also
    > has a spot to fill in for 4 vendors because we send this out to 4

    different
    > vendors at a time. So there's field Vendor 1, Vendor 2, Vendor 3, Vendor

    4.
    > Table 2 has Vendor info. Name, address, city, zip, etc...
    > So I create a form and put in all the data. I am using a combo box to call
    > the vendor table for Vendor input. Right now it shows Ven name, address,

    and
    > etc.., but only Vendor name is stored in that field.
    > All is well.
    > So now I want a report to show me everything filled out on the form, but I
    > want the addresses and tele #'s of the vendors to show up.
    > I can't get the report to show me the details of the vendor.
    > If I run a query I have to link Vendor 1 to the Ven table and I get Ven1's
    > info.
    > So I find out if I run 4 query's and change the link (relationship) I can
    > have Query Ven 1, Query Ven 2, etc...
    > So if I could run a query to run the 4 queries it would work, but it seems

    I
    > have made things more complicated than it probably has to be.
    > I am looking for another idea to try or way to link these tables together.
    > I've been racking my brain for days and just can't figure this one out.
    > HELP!
    >
     
  4. Jerid B

    Jerid B
    Expand Collapse
    Guest

    Sorry, I think I described this bad.
    Quote has a spot to list 4 vendors. We send out our request to 4 at a time.
    My vendor list has about 50 vendors loaded in it.
    So when you hit the drop down (combo) on Ven1(on Quote) it shows all 50(from
    Ventbl) and you pick one. Then you go to Ven 2 and do the same, same with 3
    and 4.
    I figure if I link the Quote table and the vendor table that should solve my
    problem, but stuck on what to link. I did try to use a vendor number
    (autonumber with vendors). Then link Ven 1, Ven 2, Ven3, Ven4 to the
    autonumber.
    The problem I run into after the link is it is not working when I add more
    than 1 link between table to table. If I link it once (example Ven1 from
    Quotetbl to Vennumber Vendortbl it will work. It is giving me (0) results
    back if I add a link from Ven2/3/4 to Vennumber.

    "Douglas J Steele" wrote:

    > The appropriate thing to do would be to have a second table that has one row
    > for each of the 4 vendors, linked to your quote table. Then, you can join
    > the 3 tables together and get all of the information you need.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    > news:A941E262-737F-41E5-949F-627523E5C0D1@microsoft.com...
    > > I will try and sum up the best I can. Access 2003.
    > > I created a "Request for Quote" database. It's rather simple.
    > > 2 Tables.
    > > Table 1 has a place for quote number, quantity, description, and date.

    > Also
    > > has a spot to fill in for 4 vendors because we send this out to 4

    > different
    > > vendors at a time. So there's field Vendor 1, Vendor 2, Vendor 3, Vendor

    > 4.
    > > Table 2 has Vendor info. Name, address, city, zip, etc...
    > > So I create a form and put in all the data. I am using a combo box to call
    > > the vendor table for Vendor input. Right now it shows Ven name, address,

    > and
    > > etc.., but only Vendor name is stored in that field.
    > > All is well.
    > > So now I want a report to show me everything filled out on the form, but I
    > > want the addresses and tele #'s of the vendors to show up.
    > > I can't get the report to show me the details of the vendor.
    > > If I run a query I have to link Vendor 1 to the Ven table and I get Ven1's
    > > info.
    > > So I find out if I run 4 query's and change the link (relationship) I can
    > > have Query Ven 1, Query Ven 2, etc...
    > > So if I could run a query to run the 4 queries it would work, but it seems

    > I
    > > have made things more complicated than it probably has to be.
    > > I am looking for another idea to try or way to link these tables together.
    > > I've been racking my brain for days and just can't figure this one out.
    > > HELP!
    > >

    >
    >
    >
     
  5. Pat Hartman\(MVP\)

    Pat Hartman\(MVP\)
    Expand Collapse
    Guest

    As Doug has already pointed out, your table is not correctly designed. You
    have what is known as a repeating group. This is a common solution when
    working with spreadsheets but with relational tables, the repeating group
    should be placed in its own table. That way you can have 0, 1, 2,3,4, or
    any number of related vendors and will never have to change your design. Do
    some reading on normalization and database design.

    To work with what you have, open the query in QBE view and add the vendor
    table FOUR times. Each time you add the table, Access will add a numeric
    suffix so that each instance can be uniquely identified. Draw four join
    lines to connect each vendor to one of the instances of the vendor table.
    To keep this info all straight in your report, you should give each of the
    four sets of data unique names. To do that, type the name into the Field
    cell of the grid and follow it with a colon ":". For example:

    Ven1Name:tblVendor.VendorName
    Ven1City:tblVendor.City
    ....
    Ven2Name:tblVendor_1.VendorName
    Ven2City:tblVendor_1.City
    ...
    Ven3Name:tblVendor_2.VendorName
    Ven3Name:tblVendor_2.VendorName
    ......
    "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    news:2740C9D3-88A1-4891-941E-099CC9D55082@microsoft.com...
    > Sorry, I think I described this bad.
    > Quote has a spot to list 4 vendors. We send out our request to 4 at a
    > time.
    > My vendor list has about 50 vendors loaded in it.
    > So when you hit the drop down (combo) on Ven1(on Quote) it shows all
    > 50(from
    > Ventbl) and you pick one. Then you go to Ven 2 and do the same, same with
    > 3
    > and 4.
    > I figure if I link the Quote table and the vendor table that should solve
    > my
    > problem, but stuck on what to link. I did try to use a vendor number
    > (autonumber with vendors). Then link Ven 1, Ven 2, Ven3, Ven4 to the
    > autonumber.
    > The problem I run into after the link is it is not working when I add more
    > than 1 link between table to table. If I link it once (example Ven1 from
    > Quotetbl to Vennumber Vendortbl it will work. It is giving me (0) results
    > back if I add a link from Ven2/3/4 to Vennumber.
    >
    > "Douglas J Steele" wrote:
    >
    >> The appropriate thing to do would be to have a second table that has one
    >> row
    >> for each of the 4 vendors, linked to your quote table. Then, you can join
    >> the 3 tables together and get all of the information you need.
    >>
    >> --
    >> Doug Steele, Microsoft Access MVP
    >> http://I.Am/DougSteele
    >> (no e-mails, please!)
    >>
    >>
    >> "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    >> news:A941E262-737F-41E5-949F-627523E5C0D1@microsoft.com...
    >> > I will try and sum up the best I can. Access 2003.
    >> > I created a "Request for Quote" database. It's rather simple.
    >> > 2 Tables.
    >> > Table 1 has a place for quote number, quantity, description, and date.

    >> Also
    >> > has a spot to fill in for 4 vendors because we send this out to 4

    >> different
    >> > vendors at a time. So there's field Vendor 1, Vendor 2, Vendor 3,
    >> > Vendor

    >> 4.
    >> > Table 2 has Vendor info. Name, address, city, zip, etc...
    >> > So I create a form and put in all the data. I am using a combo box to
    >> > call
    >> > the vendor table for Vendor input. Right now it shows Ven name,
    >> > address,

    >> and
    >> > etc.., but only Vendor name is stored in that field.
    >> > All is well.
    >> > So now I want a report to show me everything filled out on the form,
    >> > but I
    >> > want the addresses and tele #'s of the vendors to show up.
    >> > I can't get the report to show me the details of the vendor.
    >> > If I run a query I have to link Vendor 1 to the Ven table and I get
    >> > Ven1's
    >> > info.
    >> > So I find out if I run 4 query's and change the link (relationship) I
    >> > can
    >> > have Query Ven 1, Query Ven 2, etc...
    >> > So if I could run a query to run the 4 queries it would work, but it
    >> > seems

    >> I
    >> > have made things more complicated than it probably has to be.
    >> > I am looking for another idea to try or way to link these tables
    >> > together.
    >> > I've been racking my brain for days and just can't figure this one out.
    >> > HELP!
    >> >

    >>
    >>
    >>
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Whether or not you're sending the quotes to 4 vendors everytime is
    immaterial. What happens if suddenly your requirements change, and you have
    to send to 5 vendors each time? You'd have to make a table change, and
    that's time consuming.

    Realistically, you've got a repeating group (Vendor1, Vendor2, etc.), and
    that's actually a violation of database normalization principles. My
    solution of 3 tables is the "correct" one from a point of database "purity"

    If you cannot (or will not) change your design, you need to have 4 separate
    copies of your Vendor table in your query. Vendor1 gets linked to the first
    copy, Vendor2 to the second copy, and so on.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    news:2740C9D3-88A1-4891-941E-099CC9D55082@microsoft.com...
    > Sorry, I think I described this bad.
    > Quote has a spot to list 4 vendors. We send out our request to 4 at a

    time.
    > My vendor list has about 50 vendors loaded in it.
    > So when you hit the drop down (combo) on Ven1(on Quote) it shows all

    50(from
    > Ventbl) and you pick one. Then you go to Ven 2 and do the same, same with

    3
    > and 4.
    > I figure if I link the Quote table and the vendor table that should solve

    my
    > problem, but stuck on what to link. I did try to use a vendor number
    > (autonumber with vendors). Then link Ven 1, Ven 2, Ven3, Ven4 to the
    > autonumber.
    > The problem I run into after the link is it is not working when I add more
    > than 1 link between table to table. If I link it once (example Ven1 from
    > Quotetbl to Vennumber Vendortbl it will work. It is giving me (0) results
    > back if I add a link from Ven2/3/4 to Vennumber.
    >
    > "Douglas J Steele" wrote:
    >
    > > The appropriate thing to do would be to have a second table that has one

    row
    > > for each of the 4 vendors, linked to your quote table. Then, you can

    join
    > > the 3 tables together and get all of the information you need.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    > > news:A941E262-737F-41E5-949F-627523E5C0D1@microsoft.com...
    > > > I will try and sum up the best I can. Access 2003.
    > > > I created a "Request for Quote" database. It's rather simple.
    > > > 2 Tables.
    > > > Table 1 has a place for quote number, quantity, description, and date.

    > > Also
    > > > has a spot to fill in for 4 vendors because we send this out to 4

    > > different
    > > > vendors at a time. So there's field Vendor 1, Vendor 2, Vendor 3,

    Vendor
    > > 4.
    > > > Table 2 has Vendor info. Name, address, city, zip, etc...
    > > > So I create a form and put in all the data. I am using a combo box to

    call
    > > > the vendor table for Vendor input. Right now it shows Ven name,

    address,
    > > and
    > > > etc.., but only Vendor name is stored in that field.
    > > > All is well.
    > > > So now I want a report to show me everything filled out on the form,

    but I
    > > > want the addresses and tele #'s of the vendors to show up.
    > > > I can't get the report to show me the details of the vendor.
    > > > If I run a query I have to link Vendor 1 to the Ven table and I get

    Ven1's
    > > > info.
    > > > So I find out if I run 4 query's and change the link (relationship) I

    can
    > > > have Query Ven 1, Query Ven 2, etc...
    > > > So if I could run a query to run the 4 queries it would work, but it

    seems
    > > I
    > > > have made things more complicated than it probably has to be.
    > > > I am looking for another idea to try or way to link these tables

    together.
    > > > I've been racking my brain for days and just can't figure this one

    out.
    > > > HELP!
    > > >

    > >
    > >
    > >
     
  7. Jerid B

    Jerid B
    Expand Collapse
    Guest

    Thank you... I will do some reading into that. Your solution worked in the
    mean time. I will however check into repeating groups. I just couldn't think
    of a better way of doing this. However I will check into how I can change
    things. Thanks for the information.

    "Pat Hartman(MVP)" wrote:

    > As Doug has already pointed out, your table is not correctly designed. You
    > have what is known as a repeating group. This is a common solution when
    > working with spreadsheets but with relational tables, the repeating group
    > should be placed in its own table. That way you can have 0, 1, 2,3,4, or
    > any number of related vendors and will never have to change your design. Do
    > some reading on normalization and database design.
    >
    > To work with what you have, open the query in QBE view and add the vendor
    > table FOUR times. Each time you add the table, Access will add a numeric
    > suffix so that each instance can be uniquely identified. Draw four join
    > lines to connect each vendor to one of the instances of the vendor table.
    > To keep this info all straight in your report, you should give each of the
    > four sets of data unique names. To do that, type the name into the Field
    > cell of the grid and follow it with a colon ":". For example:
    >
    > Ven1Name:tblVendor.VendorName
    > Ven1City:tblVendor.City
    > ....
    > Ven2Name:tblVendor_1.VendorName
    > Ven2City:tblVendor_1.City
    > ...
    > Ven3Name:tblVendor_2.VendorName
    > Ven3Name:tblVendor_2.VendorName
    > ......
    > "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    > news:2740C9D3-88A1-4891-941E-099CC9D55082@microsoft.com...
    > > Sorry, I think I described this bad.
    > > Quote has a spot to list 4 vendors. We send out our request to 4 at a
    > > time.
    > > My vendor list has about 50 vendors loaded in it.
    > > So when you hit the drop down (combo) on Ven1(on Quote) it shows all
    > > 50(from
    > > Ventbl) and you pick one. Then you go to Ven 2 and do the same, same with
    > > 3
    > > and 4.
    > > I figure if I link the Quote table and the vendor table that should solve
    > > my
    > > problem, but stuck on what to link. I did try to use a vendor number
    > > (autonumber with vendors). Then link Ven 1, Ven 2, Ven3, Ven4 to the
    > > autonumber.
    > > The problem I run into after the link is it is not working when I add more
    > > than 1 link between table to table. If I link it once (example Ven1 from
    > > Quotetbl to Vennumber Vendortbl it will work. It is giving me (0) results
    > > back if I add a link from Ven2/3/4 to Vennumber.
    > >
    > > "Douglas J Steele" wrote:
    > >
    > >> The appropriate thing to do would be to have a second table that has one
    > >> row
    > >> for each of the 4 vendors, linked to your quote table. Then, you can join
    > >> the 3 tables together and get all of the information you need.
    > >>
    > >> --
    > >> Doug Steele, Microsoft Access MVP
    > >> http://I.Am/DougSteele
    > >> (no e-mails, please!)
    > >>
    > >>
    > >> "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    > >> news:A941E262-737F-41E5-949F-627523E5C0D1@microsoft.com...
    > >> > I will try and sum up the best I can. Access 2003.
    > >> > I created a "Request for Quote" database. It's rather simple.
    > >> > 2 Tables.
    > >> > Table 1 has a place for quote number, quantity, description, and date.
    > >> Also
    > >> > has a spot to fill in for 4 vendors because we send this out to 4
    > >> different
    > >> > vendors at a time. So there's field Vendor 1, Vendor 2, Vendor 3,
    > >> > Vendor
    > >> 4.
    > >> > Table 2 has Vendor info. Name, address, city, zip, etc...
    > >> > So I create a form and put in all the data. I am using a combo box to
    > >> > call
    > >> > the vendor table for Vendor input. Right now it shows Ven name,
    > >> > address,
    > >> and
    > >> > etc.., but only Vendor name is stored in that field.
    > >> > All is well.
    > >> > So now I want a report to show me everything filled out on the form,
    > >> > but I
    > >> > want the addresses and tele #'s of the vendors to show up.
    > >> > I can't get the report to show me the details of the vendor.
    > >> > If I run a query I have to link Vendor 1 to the Ven table and I get
    > >> > Ven1's
    > >> > info.
    > >> > So I find out if I run 4 query's and change the link (relationship) I
    > >> > can
    > >> > have Query Ven 1, Query Ven 2, etc...
    > >> > So if I could run a query to run the 4 queries it would work, but it
    > >> > seems
    > >> I
    > >> > have made things more complicated than it probably has to be.
    > >> > I am looking for another idea to try or way to link these tables
    > >> > together.
    > >> > I've been racking my brain for days and just can't figure this one out.
    > >> > HELP!
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
  8. Jerid B

    Jerid B
    Expand Collapse
    Guest

    Understand now...
    I did start a new database and made 3 tables:
    Quote (with quantity, description, etc..)
    Vendor (address, telephone, etc..)
    Vendor Number (Vendor 1, Vendor 2, etc...)
    I still don't now how to link... I went from having to link Vendor to Quote,
    to Vendor Number to Quote and running inot the same problem.
    I think I am way over my head and will do some reading... thanks for the
    tips and I will defianitly be using the 3 tables.


    "Douglas J Steele" wrote:

    > Whether or not you're sending the quotes to 4 vendors everytime is
    > immaterial. What happens if suddenly your requirements change, and you have
    > to send to 5 vendors each time? You'd have to make a table change, and
    > that's time consuming.
    >
    > Realistically, you've got a repeating group (Vendor1, Vendor2, etc.), and
    > that's actually a violation of database normalization principles. My
    > solution of 3 tables is the "correct" one from a point of database "purity"
    >
    > If you cannot (or will not) change your design, you need to have 4 separate
    > copies of your Vendor table in your query. Vendor1 gets linked to the first
    > copy, Vendor2 to the second copy, and so on.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    > news:2740C9D3-88A1-4891-941E-099CC9D55082@microsoft.com...
    > > Sorry, I think I described this bad.
    > > Quote has a spot to list 4 vendors. We send out our request to 4 at a

    > time.
    > > My vendor list has about 50 vendors loaded in it.
    > > So when you hit the drop down (combo) on Ven1(on Quote) it shows all

    > 50(from
    > > Ventbl) and you pick one. Then you go to Ven 2 and do the same, same with

    > 3
    > > and 4.
    > > I figure if I link the Quote table and the vendor table that should solve

    > my
    > > problem, but stuck on what to link. I did try to use a vendor number
    > > (autonumber with vendors). Then link Ven 1, Ven 2, Ven3, Ven4 to the
    > > autonumber.
    > > The problem I run into after the link is it is not working when I add more
    > > than 1 link between table to table. If I link it once (example Ven1 from
    > > Quotetbl to Vennumber Vendortbl it will work. It is giving me (0) results
    > > back if I add a link from Ven2/3/4 to Vennumber.
    > >
    > > "Douglas J Steele" wrote:
    > >
    > > > The appropriate thing to do would be to have a second table that has one

    > row
    > > > for each of the 4 vendors, linked to your quote table. Then, you can

    > join
    > > > the 3 tables together and get all of the information you need.
    > > >
    > > > --
    > > > Doug Steele, Microsoft Access MVP
    > > > http://I.Am/DougSteele
    > > > (no e-mails, please!)
    > > >
    > > >
    > > > "Jerid B" <JeridB@discussions.microsoft.com> wrote in message
    > > > news:A941E262-737F-41E5-949F-627523E5C0D1@microsoft.com...
    > > > > I will try and sum up the best I can. Access 2003.
    > > > > I created a "Request for Quote" database. It's rather simple.
    > > > > 2 Tables.
    > > > > Table 1 has a place for quote number, quantity, description, and date.
    > > > Also
    > > > > has a spot to fill in for 4 vendors because we send this out to 4
    > > > different
    > > > > vendors at a time. So there's field Vendor 1, Vendor 2, Vendor 3,

    > Vendor
    > > > 4.
    > > > > Table 2 has Vendor info. Name, address, city, zip, etc...
    > > > > So I create a form and put in all the data. I am using a combo box to

    > call
    > > > > the vendor table for Vendor input. Right now it shows Ven name,

    > address,
    > > > and
    > > > > etc.., but only Vendor name is stored in that field.
    > > > > All is well.
    > > > > So now I want a report to show me everything filled out on the form,

    > but I
    > > > > want the addresses and tele #'s of the vendors to show up.
    > > > > I can't get the report to show me the details of the vendor.
    > > > > If I run a query I have to link Vendor 1 to the Ven table and I get

    > Ven1's
    > > > > info.
    > > > > So I find out if I run 4 query's and change the link (relationship) I

    > can
    > > > > have Query Ven 1, Query Ven 2, etc...
    > > > > So if I could run a query to run the 4 queries it would work, but it

    > seems
    > > > I
    > > > > have made things more complicated than it probably has to be.
    > > > > I am looking for another idea to try or way to link these tables

    > together.
    > > > > I've been racking my brain for days and just can't figure this one

    > out.
    > > > > HELP!
    > > > >
    > > >
    > > >
    > > >

    >
    >
    >
     

Share This Page