Forum Discussion
Multiple relationships to the same table for each row
I have a database with a design similar to this:
For each record, I have two fields that are related to the same table (a directory of people). The Seller and Manufacturer fields are defined as lookups to the people table.
Now, I want to do a query that will display, for Seller and Manufacturer, the FirstName, LastName and e-mail fields of the related record for the Seller and Manufacturer.
So, if I have a product table that looks like this:
| ID | Name | Seller | Manufacturer |
| 1 | ProductA | 1 | 2 |
And a people table that looks like that:
| ID | FirstName | LastName | |
| 1 | Bob | Smith | fake email |
| 2 | Kelly | Jones | fake email |
I'd like to get this:
| Product | Seller First | Seller Last | Seller e-mail | Man First | Man Last | Man e-mail |
| ProductA | Bob | Smith | fake email | Kelly | Jones | fake email |
I thought this was goind to be simple, but I simply cannot figure out how to do this!
Can anyone help?
First, this is a suboptimal approach: " The Seller and Manufacturer fields are defined as lookups to the people table." Lookups are an inferior way to manage related values in tables. While it is true that Microsoft implemented them a long time ago, and a lot of people have been lured into using them, it's simply not a good design. Return to the basic Primary Key/Foreign Key designation and save a lot of grief for yourself.
That said, the solution to creating a query here is to add the People table to the query TWICE. Alias the two instances as "Seller" and "Manufacturer" so you know which is which. Then join Primary Key of the Seller instance to the Seller Foreign Key and the Primary Key of the Manufacturer instance to the Manufacturer Foreign Key.
First, this is a suboptimal approach: " The Seller and Manufacturer fields are defined as lookups to the people table." Lookups are an inferior way to manage related values in tables. While it is true that Microsoft implemented them a long time ago, and a lot of people have been lured into using them, it's simply not a good design. Return to the basic Primary Key/Foreign Key designation and save a lot of grief for yourself.
That said, the solution to creating a query here is to add the People table to the query TWICE. Alias the two instances as "Seller" and "Manufacturer" so you know which is which. Then join Primary Key of the Seller instance to the Seller Foreign Key and the Primary Key of the Manufacturer instance to the Manufacturer Foreign Key.
- Yes, that's it! And thanks also for the best practice advise. I really thought that lookups, although not found in other database software, was the recommended way for Access. I learned something new!
Ah, "recommended". Like a lot of things, it depends on who is doing the recommending and why.
For extremely basic situations, Lookup fields in tables are relatively harmless in the sense that such applications require little logic or data manipulation. However, with that simplicity comes significant limitations on flexibility and extensibility and usability.
Some long-time developers are quite adamant. Although less insistent, I personally don't use them (except in one very limited situation) and never recommend them outside that limited situation. I'll restrain my impulse to preach further. It's worth while, though, to browse the internet for further discussions if you have time.
