Help with relationship scenario & effects on queries/reports

[DBheadache]

I have a 2 table database I'm trying to get up and running and I simply can't figure out how to get it to work how I'd like it to and can't for the life of me come up with a good set of search keywords to find what I need.



The structure is as follows:

Items_Table
-Item ID (PK)
-Manufacturer (FK)
-Model
....
-Purchased From (FK)
-Sold To (FK)

Contacts_Table
-Contact ID (PK)
-Business Name
-First Name
-Last Name
-Tax ID
....


The unusual nature of this is that a contact could be a manufacturer of one item, a buyer (sold to) of a different item, and a seller (purchased from) of yet another item. The web is teeming with examples of orders, customers, items, and categories but they all seem to be from the more common vantage point of a retail store or distributor that buys product made by Z from X distributor and sells to Y. Where as my scenario is more fluid and there's a lot of back and forth and while most contacts won't be manufacturers and buyers/sellers there are cases of that and there's plenty of contacts that are both buyers/sellers.

The problems I'm having arise when I need to query or create a report. The reports need to include details about the item from the items table including a column for purchased from, sold to, manufacturer, etc. But it needs to include not just the business or individual name, I need it to also show other details from the contacts table like Tax ID. I can't figure out how to make a query that involves all this information. The sticking point seems to me that how would access know to use put in details from contact 1 as the seller, contact 23 as the manufacturer, and maybe no contact info for the buyer since the item has not been sold. The report I most want is of every single item that has ever been logged in regardless of whether or not it is still in-hand or sold. I don't want to add too many more questions to this as to complicate things more so I'll leave it at that for now.

[pbaldy]

In a query, you'd add the contacts table to the grid 3 times. Access will alias the second and third copies like "Contacts_1", which you can change. Join each of the fields in the items table to a different instance of the contacts table. You can then get the appropriate fields from each by specifying both table and field, like Contacts_1.BusinessName. I'd alias the fields so they make sense in the final query.