MULTIPLE LOOKUPS

[Guest]

I have a table which contains three fields "supplyer"," manufacturer",and
"subcontractor". in addition, i have a company table which contains all the
companys that include supplier, manufacturer, and subcontractors.

I have designed a query to seperate from the company table all companies
that are suppliers, manufacturers, and or subcontractors.

now, the three fields of the table are set to look up companys from the
three new query's created. Each will select from companies that have been
queried for that fields need.

The problem comes in that when I creat a querry that includes the record
table please these three queries I cannot get it to query correct. How can I
use one table which containes all the companies for multiples field of a
table and work. I dont want to creat three tables because one company may do
all three tasks and I will have to enter the information three times.

 

[Jeff Boyce]

It sounds like your data looks like a spreadsheet. To use the strengths
that Access offers, you may need to re-think your data design. Take a look
at the topic of normalization.

It sounds like you have companies, as one entity (hence, one table), you
have "roles" (your supplier, etc.) as another table, and a third table to
hold the valid combinations thereof. A rough idea:

tblCompany
CompanyID
CompanyName
(other company-only info)
...

tlkpRole
RoleID
Role (your 'supplier', 'manufacturer', 'subcontractor', AND any other
roles you add)
(any other information about a specific Role)

trelCompanyRole
CompanyID
RoleID
(any other info about THIS company and THIS role, in combination)

You'd have one row per entity (i.e., one row per company, one row per role,
one row per valid company-role combination). If your CompanyA has all three
roles, you'd have three rows in that "junction" table.