How to handle multiple fields that look up to same table

[ChristineP]

To illustrate table look-ups and relationships, most examples I've ever seen
use EmployeeID as the example, with the EmployeeID looking to an Employee
table for last name, first name and a relationship based on the EmployeeID. A
query is then typically shown to get, for example, "Smith, John" onto a form.

Well, what is the best way to handle it when you have 5 fields for storing
an EmployeeID, each of which looks to the same Employee table? And, do you
need 5 separate queries in order to show "Smith, John", rather than the
EmployeeID in forms and reports?

Thanks.

 

[Duane Hookom]

If I understand correctly, you have five fields in one table that all store
an EmployeeID value that is related to an Employee table. This sounds
un-normalized but I could be wrong. There is nothing that says you can't
create relationships to 5 copies of the Employee table.

Keep in mind that most of us with lots of Access experience would not use
lookup fields in table design.

 

[John Vinson]

To illustrate table look-ups and relationships, most examples I've ever seen
use EmployeeID as the example, with the EmployeeID looking to an Employee
table for last name, first name and a relationship based on the EmployeeID. A
query is then typically shown to get, for example, "Smith, John" onto a form.

Well, what is the best way to handle it when you have 5 fields for storing
an EmployeeID, each of which looks to the same Employee table? And, do you
need 5 separate queries in order to show "Smith, John", rather than the
EmployeeID in forms and reports?

Thanks.

No, you need ONE query. Just join the Employee table to this table
*five times* - add it to the query grid repeatedly; join each instance
to the appropriate ID field.

John W. Vinson[MVP]

 

[ChristineP]

Duane, why is it not good practice to use look-up fields? I thought the
reason for doing this was so you are only storing a number (in my case, the
primary key/autonumber for each employee), rather than the text of the name
which would take up more "space". If you don't use look-ups, how do you store
other info about employees?

Christine

 

[Duane Hookom]

I would use combo boxes on forms rather than defining lookups in tables.
Check this link http://www.mvps.org/access/lookupfields.htm.