Different Manufacturers in 1 query

[mrmims]

I have tbl_Manufacturers which supplies manufacturer info to many tables like tbl_gearbox, tbl_limit_box, tbl_motor, etc. I have many manufacturers which supply multiple devices and pieces.



In each table I have GearboxManuID, LimitBoxManuID, MotorManuID, etc., each relates to ManufacturerID in tbl_Manufacturer.

I have query qry_serial_numbers which queries all data from all the tables for each serial number, including the manufacturer data for all the elements.

How do I query the manufacturer for gearboxes, limit boxers, motors, etc., and have the query return the ManufacturerName and not just the ManufacturerID? When I ask the query to return ManufacturerName for the different pieces, it obviously won't because of it is confused by the multiple relationships I have going back to my tbl_Manufacturer.

Or is it possible to have the query return the ManufacturerID, but have the form use the ManufacturerID to return the Manufacturer Name?

Does any of that make sense?

Thank you

[orange]

You need to structure your database to support your business. Getting your tables and relationships designed to do so is not trivial.
Here is a link that shows you with examples and solutions how to design a database.

You may also wish to research queries that involve more than 1 table. Youtube video

Good luck with your project.

[ssanfu]

I want to reiterate what orange said. The table structure/relationships is crucial to the success of your dB.

There are some tutorials at Roger Carlson's site that might help.
http://www.rogersaccesslibrary.com/forum/forum46.html

Your description is somewhat confusing. A picture of the relationship window would help.

At this point, it sounds like you (should) have two tables...
One manufacturer can have many types of equipment AND
One type of equipment can be made by many manufacturers.
This is a many to many relationship.

Consider:

tbl_Manufacturers
ManufacturerID_PK -  Autonumber
ManName - Text
ManAddress - Text
ManCity - Text
.... other fields


tbl_Equiptment
EquipID_PK -  Autonumber
EquiptType - Text
.... other fields


tbl_EquipManu (junction table)
EquipManuID_PK -  Autonumber
ManufacturerID_FK - Long
EquipID_FK - Long
SerialNumber - Text

qry_serial_numbers:

SELECT tbl_Manufacturers.ManName, tbl_Equiptment.EquiptType, tbl_EquipManu.SerialNumber
FROM tbl_Manufacturers INNER JOIN (tbl_Equiptment INNER JOIN tbl_EquipManu ON tbl_Equiptment.EquipID_PK = tbl_EquipManu.EquipID_FK) ON tbl_Manufacturers.ManufacturerID_PK = tbl_EquipManu.ManufacturerID_FK
ORDER BY tbl_Manufacturers.ManName, tbl_Equiptment.EquiptType;