Forum Discussion
Multiple relationships to the same table for each row
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.
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.
