Need Help With Table Relationships

[waholtmn]

I have two tables. The first table has two fields; Implementer and Originator names. The second table is roster of employee names with their job class, location, and division. I want to perform a query to find both the Implementer's and Originators job class, location, and division. I have tried to setup relationship with the implementer's name field in table A to the employee's name field in table B and the originator's name field in table A to the same employee name field in table B but its not working right. I am only seeing the Implementer's job class, location, and division for the query results (It's essentially being duplicated). What are my options for a layman?

Wayne

 

[pbaldy]

In query design view, you want to add the first table once and the second table twice (Access will add _1 to the second instance as an alias). Join each of the two fields in the first table to a different instance of the second, which should let you get both sets of info.

 

[plog]

First, on behalf of all the John Smiths of the world, use a numeric ID field instead of text field to link your tables. This will bite you later on. Plus, what happens when Jill Jones gets married, divorced or joins the Nation of Islam and gets a new name? In your second table create a field called EmployeeId that's an autonumber. Then use it instead of people's names in other tables.

With that said, to solve your issue you need to bring in 2 instances of your second table. Starting from scratch this is what you would do:

1. open new query in design view, bring in first table, second table and second table again. The second instance will suffix the name with '_1' to differentiate it from the first instance.

2. link from the implementer field to the first instance of the second table

3. link from the originator field to the second instance (_1) of the second table.

4. bring down all the fields from both instances of the second table and run your query.

Again, primary key autonumbers instead of text.

 

[pbaldy]

+1 on using a numeric key rather than name. I hadn't noticed that.

 

[waholtmn]

Awesome. Worked like a charm!