Ambiguous Join nightmare 1

[blaine011]

I have a database that takes a main [order information] table, and two customers.

I create a form with an underlying querie that pulls all information from the [Order Information] Table, and two separate customers tables. Both customer tables, cust1 and cust2, are related to a city, province and country table. When I have just one customer and the main table in the query, with the customer pulling address information from the city prov and country tables it works fine.

As soon as I add cust2 into the query which also draws on the city prov and country tables, I get the ambiguous outer join error, and it tells me to first create a query joining the first table, then add that to the new query.
What does this mean?

 

[scriverb]

You are probably using an INNER join(straight line) in the QDW between the Cust1 table and the city,province, and country tables. That works just fine. But, if you add Cust2 and create the same INNER join to the same tables you are creating a situation that ACCESS can't resolve. Which records from the city,province, and country tables shoudl be returned? The one linked to Cust1 or the one linked to Cust2?

I believe you can fix this by adding the three tables again to the query, ACCESS will rename them with a 1 on the end of the names, and you can link Cust2 to these three tables. Now there is no conflict with the joins. You are linking to the same tables but returning two different records of data.

Post back with the results of this change.

Bob Scriver
[blue]Want the best answers? See FAQ181-2886[/blue]

 

[blaine011]

I thought I tried that before but I guess not, at any rate it is working now. You are a godsend, thanks alot!