Main Table References Another Table Multiple Times

[nogreatnamesleft]

I have a main table with several fields for people - i.e. Engineering, CAD,
Manager, etc. The fields contain a number to represent an individual. A
separate table has, as its "key", the number identifying the individual along
with the name of the person. This table contains all Engineers, CAD, etc.

Now I want to create a query to replace the number with the name. However, I
have no idea how the best way to do this would be. I've tried using "Show
Table" and showing multiple instances of the "People" table and linking each
instance of the "People" table to the fields in the "Main" table, but Access
doesn't accept this.

Any suggestions please?

 

[Dale Fye]

You'll have to include multiple copies of the "People" table in the query.
Link each copy to only one of the fields in the main table. You might even
want to go so far as to alias the table names so you don't see:

People.FirstName, People.LastName
and
People_1.FirstName, People_1.Lastname

 

[Duane Hookom]

First, IMO, your table structure is wrong if you have fields with names of
job titles. I would expect a related tables with fields like:

foreignkey - links to main table primary key
JobID - links to table of job types
PeopleID - links to table of People

This structure would resolve your issue since each table would be joined
only once.

You could create a normalizing union query to dynamically create the above
structure.

I'm not sure what you mean by "Access doesn't accept this". You should be
able to link multiple instances of the same table to your main table.

 

[nogreatnamesleft]

This worked. I found I had Access rejecting my query for a different reason.
Thanks.

 

[nogreatnamesleft]

Not my tables. I linked to someone else's tables on an SQL server.