Table with multiple fields using same lookup table 1

[nastar1]

tblWA includes many fields including the following:

Originator
POC
Manager

each of those fields were setup using a combobox lookup to tblNames to display the person's name.

tblNames includes the firstname, lastname, office and phone.

When I create the relationships between tblWA and those 3 fields to the tblNAMES I get two additional instances of tblNAMES called tblNAMES_1 and tblNAMES_2.

I'm having no luck writing queries that use those additional table instances and now wondering if my table concept is in need of complete restructure.

I haven't been able to locate any sample databases that show a situation where multiple fields in one table use another table. Northwinds uses two separate personnel tables for employees and suppliers, so it doesnt match what I'm trying to do.

 

[lespaul]

When using the relationship diagram tool to create a relationship like this the _1 and _2 are alias table names. In the SQL view you can join to these tables like this:

SELECT *, N1.Name As OriginatorName, N2.Name AS POCName 
FROM tblWA W
INNER JOIN Names as N1 on W.Originator = N1.OriginatorID
INNER JOIN Names as N2 on W.POC = N2.POCID

This will get the Name field from the Names table for the Origiator when joined as N1 and the name of the POC from N2.

See how that works?



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[dhookom]

Leslie deserves a star for providing a working solution.

A more relational solution would be to take those fields out of tblWA and create a related table with the primary key value from tblWA, a value (Originator, POC, or Manager)
and the primary key from tblNames.

Duane MS Access MVP
Now help me support United Cerebral Palsy