Outer Join with More than 2 Tables "Operator Missing"

[WillAtlGuy]

I am trying to perform a summary query that joins 4 tables. Ordinarily I don't work with Access. With DB2, I would write something like this:

SELECT customer.customerNo,
customer.name,
Sum(customerContact.ordersPlaced) AS Orders,
Sum(productSales.qtySold) AS Sales,
Sum(productReturns.qtySold) AS Returns
FROM customer
LEFT OUTER JOIN customerContact ON customer.customerNo=customerContact.customerNo
LEFT OUTER JOIN productReturns ON customer.customerNo=productReturns.customerNo
LEFT OUTER JOIN productSales ON customer.customerNo=productSales.customerNo
GROUP BY customer.customerNo, customer.name

But Access doesn't like that syntax and gives me an error: "missing operator".

I have searched the forums and found that most people recommend adding parenthesis like the following:

SELECT customer.customerNo,
customer.name,
Sum(customerContact.ordersPlaced) AS Orders,
Sum(productSales.qtySold) AS Sales,
Sum(productReturns.qtySold) AS Returns
FROM ((customer
LEFT JOIN customerContact ON customer.customerNo=customerContact.customerNo)
LEFT JOIN productReturns ON customer.customerNo=productReturns.customerNo)
LEFT JOIN productSales ON customer.customerNo=productSales.customerNo
GROUP BY customer.customerNo, customer.name;

But this produces the wrong result. It looks like it is creating nested join where table2 is joined to table1, table3 to table2, and table4 to table3. What I want is tables2, 3 and 4 to each be joined to table1.

The queries here are from an example application I created to illustrate the problem.

Any help would be greatly appreciated!

 

[IFELSETHEN]

Hi,
I don't have time to try this but you might want to....

SELECT a.customerNo,
a.name,
Sum(b.ordersPlaced) AS Orders,
Sum(d.qtySold) AS Sales,
Sum(c.qtySold) AS Returns
FROM
customer a, customerContact b, productReturns c, productSales d
WHERE
a.customerNo = b.customerNo
AND
a.customerNo = c.customerNo
AND
a.customerNo = d.customerNo
GROUP by a.customerNo, a.name

*******************************************
If this doesn't work then look at the 'HAVING'.
John

*********************
John Nyhart

http://www.nyhart.com

*********************

 

[IFELSETHEN]

I just notice that this would require that there were records for the customer in all four tables. This may not work ..... try 'HAVING' .... John

*********************
John Nyhart

http://www.nyhart.com

*********************

 

[WillAtlGuy]

As far as I know, you can't use HAVING to create a join condition. I'll give it a try, but the HAVING will probably be performed after the join and without a WHERE or ON clause, you get a cartesian join. So you will end up with every row of every table joined with every row of every other table. So 4 tables with 100 rows each becomes 100,000,000 rows.