More than 2 tables in a query gives errors. Way round this?

[TheFoxy]

Hey,

I currently have a query with several sub-queries that each add data from a different table to the sub-query before them, to build up information from several tables in one query. I've had to do this because using more than 2 tables in a query gives me an 'ambiguous outer join' error. Is there any way to use more than 2 tables in the query without getting the error? Using multiple sub-queries is a right pain and it makes the database very messy.

 

[Golom]

Access requires parentheses to figure out how to parse things when there are multiple tables. For example
[tt]
FROM A1 LEFT JOIN A2 ON A1.F1 = A2.F1
LEFT JOIN A3 ON A2.F2 = A3.F2
[/tt]
won't work, but
[tt]
FROM (A1 LEFT JOIN A2 ON A1.F1 = A2.F1)
LEFT JOIN A3 ON A2.F2 = A3.F2
[/tt]
will.

 

[TheFoxy]

lol, that use of 'FROM' is something that I have not seen before. Could you give me an explanation so that I can set up this query?

 

[Golom]

I guess so ... but an explanation of what, exactly?

If you're getting 'ambiguous outer join' messages now then you must have a FROM clause in your SQL already (assuming its a SELECT).

It may be more instructive if you paste the SQL you're using here. We can then see where you are having problems rather than launching into a long winded dissertation on the inner mysteries of joins which is really beyond the scope of a forum like this.

 

[TheFoxy]

Here is the SQL code for the 4 queries which I would like to merge into one single one. (I had to split them up this way to avoid the error). Each query takes the results of the one before it and adds information from another table, so I end up with all the information I need in the last query.

SELECT Services.*
FROM Services
WHERE (((Services.ServiceID)=[Please enter Service ID]));

SELECT [SubQuery1].*, Items.*
FROM [SubQuery1] INNER JOIN Items ON [SubQuery1].ItemSerialNumber = Items.ItemSerialNumber;

SELECT [SubQuery2].*, Customers.*
FROM [SubQuery2] INNER JOIN Customers ON [SubQuery2].CustID = Customers.CustID;

SELECT [SubQuery3].*, Components.*
FROM [SubQuery3] INNER JOIN Components ON [SubQuery3].ComponentType = Components.ComponentType;

(Note: Each Item only has one ComponentType).

 

[TheFoxy]

The top query is SubQuery 1, the one below it SubQuery2, the one below that SubQuery3, and the last one is the final query with all the required data in it.

 

[Golom]

Try something like
[tt]
SELECT *

FROM ((Services S INNER JOIN Items I ON S.ItemSerialNumber = I.ItemSerialNumber)
INNER JOIN Customers C ON C.CustID = I.CustID)
INNER JOIN Components N ON N.ComponentType = C.ComponentType

WHERE S.ServiceID = [Please enter Service ID]
[/tt]
I have had to guess the following.

[li]In the second join ... "CustID" is from the Items table. (It could be from the Services or Items tables)[/li]
[li]In the third join ...ComponentType is from the "Customers" table (it could be from Customer, Items or Services.)[/li]