Help with relationships!!!

[skier4life]

Hi guys,
Here is my problem. As part of my internship at an investment firm I'm creating a database that contains all the major timberland owners in the US. My main table has about 300 firms/organizations that own timberland. My primary key in that table is simply an auto number that gives each entry a unique "Owner ID". One of the tables I'm trying to link in has all recent timberland transactions. I painstakingly went through the entire transactions table and looked for buyers and sellers that I already had in my main timberland owners database. If I found one, I entered the unique owner ID next to either the buyer or the seller. Therefore, I have two fields, buyer and seller ID that match up to the numbers in the primary key of my main table. What I want to do is form relationships between the owner ID column in my main table and both the seller and the buyer ID columns in my second table. That way, I can preform queries such as "Give me all recent transactions that Plum Creek was involved in". I would enter in plum creek under criteria for firm/organization in the field from my main table, and I would want the search to pull any line from my second table where plum creek was listed as the buyer or seller. This works if I only have one relationship say between the "owner ID" and "Seller ID" fields, but then I can only bring up recent transactions where plum creek is the seller. What I want is it to pull from both seller ID and buyer ID columns when there is a match with plum creek. Any time I try this double relationship, I either get no results since it wants both the seller ID and the buyer ID to match (which will never happen) or I get an error about an ambiguous outer join. Any idea how to do this?? Do i need two different recent transaction tables? Any help would be MUCH appreciated, and my boss would love me

[pbaldy]

You need the same relationship in the query that you have in relationships: an aliased copy of the Firm/Organization table.

[rpeare]

you've created a circular reference in the middle image, just like your references you need to add your firm/organization table again and link it appropriately (BuyerID to Timb Owner ID)

Just a couple of notes. You really, really want to stay away from spaces and other special characters (/ for example) ANY database object names (table names, field names, report names, query names etc) they can cause a lot of problems, for instance / indicates division. It's better to stay away from them if at all possible.