WillAtlGuy
Programmer
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!
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!