Help showing results

[Jerid B]

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!

 

[Douglas J Steele]

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.

 

[Jerid B]

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.

 

[Pat Hartman\(MVP\)]

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
......

 

[Douglas J Steele]

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.

 

[Jerid B]

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.

 

[Jerid B]

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.