Missing data on report from table that uses combobox Access 97

[canblue]

Looking for some help. I have a SUPPLIER table that has an ID code and supplier name. In another table, PARTS, I allow up to 3 suppliers per part. I use a combobox to allow only the supplier name to be selected. When I open the PARTS table or run a query against it, the data looks fine( part name and supplier names). When I create a report from the parts table I only get the supplier ID and not the name. I cannot figure out how to get the supplier name.

Thanks.

[nico5038]

You'll have to create a query with the PARTS tabel and three times the SUPPLIER table.
Now drag and drop Supplier1 to the first table, Supplier2 to the second table, etc

OPlace now the names from each of the SUPPLIER tables in the query and use that query for your report.

Nic;o)

[canblue]

Nic, thanks for the reply. Sometimes things click and I understand but this is not the case.

Supplier Table ID Name
001 Bob
002 Ted
003 Brad
004 Tony
005 John
Parts Table Part Sup1 Sup2 Sup3( can have 3 suppliers for any 1 part)
Seal Ted Brad John

The suppliers in the Parts table are selected from a combobox that is linked to the Supplier table. When I ran the report against the parts table I got:
Part Name Seal Sup1 002 Sup2 003 Sup3 005
I was looking for:
Part Name Seal Sup1 Ted Sup2 Brad Sup3 John

How can I establish a link back to the Supplier table to "resolve" the name in the report? I can post additional info if required. Any alternate suggestions on approaches?

This needs to be a "programmable" solution as the parts list will grow and change.

Thanks,
Bryan

[nico5038]

Hmm, your Q looked like you couldn't get the names of all three :-)
You're paying here the price for not normalizing your data properly.
You should have created a separate PartsSupplier table with the key from both tables. The advantage is that you can even add more than 3 suppliers when needed :-)

To make it work in this situation you'll have to create a UNION query like:

select partid, supplier1 as supplier from tbl parts
UNION
select partid, supplier2 as supplier from tbl parts
UNION
select partid, supplier3 as supplier from tbl parts;

Now you'll get the possibility to select all rows from the same supplier by basing your report on this query.

Clearer ?

Nic;o)

[canblue]

Much clearer. Was thinking along those lines but I could not formulate the syntax Spent a few years immersed in DB2 and I tend to think in those terms for SQL and it makes things difficult at times. Very interested in your suggestion about the table.
I could not think of a way to include the supplier code(key) along with the name when I used the combo box to add the information into the parts table. The parts table has an Autonumber key but it has not reference to the supplier table. Can you point me towards an example of this technique?

Thanks,

Bryan

[canblue]

I was hoping to include an image or copied text in my note but I do not see any copy/ paste option that are available. I guess that I could put the material in a Word doc and attach that. Would that be of any use or interest to you?

Bryan

[nico5038]

The normalize way would have three tabels:

Parts:
PartID Description

Supplier
SupplierID SupplierName

PartsSupplier
PartID SupplierID

This way you can use a JOIN of the three tables to get all information in one row.

For the UNION query you can use also a JOIN to include the name like:

Select A.PartID, B.SupplierName from Parts A INNER JOIN Supplier B ON A.Supplier1 = B.SupplierID
UNION
Select A.PartID, B.SupplierName from Parts A INNER JOIN Supplier B ON A.Supplier2 = B.SupplierID
UNION
Select A.PartID, B.SupplierName from Parts A INNER JOIN Supplier B ON A.Supplier3 = B.SupplierID

No need to put it in Word, guess I got the view :-)

Nic;o)

[canblue]

Nic:
Many thanks. The re-structure of the tables worked out just fine and reduced
the report query complexity. Results as wanted.

Best Regards,

Bryan

[nico5038]

Glad I could help and success with your application !

Nic;o)