[AccessD] SQL: Sum from the third table

Ervin,
 
SELECT Customer.CustID,
             Customer.Name,
             Sum(Order.ValAmount) AS UkVred,
             Sum(OrderDet.Quantity) AS ukkol
FROM (Customer INNER JOIN [Order] ON Customer.CustID = Order.CustID)
           INNER JOIN OrderDet ON Order.RedBROJ = OrderDet.RedBROJ
GROUP BY Customer.CustID, Customer.Name;
 
Hope this helps.
 
Scott

-----Original Message-----
From: Ervin Brindza [mailto:viner at eunet.yu]
Sent: Wednesday, July 23, 2003 2:53 AM
To: AccessD at databaseadvisors.com
Subject: [AccessD] SQL: Sum from the third table


Hi,
I'm struggling with a simple SQL expression, hope someone can help:
There are 2 tables: 
- Order(fields: RedBROJ(Autonumber), CustId, ValAmount) - RedBROJ is a join
field
- Customer(CustId, Name) 
and OrderDet(RedBROJ, quantity)
and I want to list every customer, with the sum of field ValAmount from
table Order and with the sum of field quantity from the OrderDet.
But the following:
SELECT Order.CustId, Customer.Name,  Sum([Order].[ValAmount]) AS UkVred,
(select sum(quantity) from OrderDet  WHERE OrderDet.RedBROJ=Order.RedBROJ)
AS ukkol
FROM Customer INNER JOIN Order ON Customer.CustId= Order.CustId
GROUP BY Order.CustId, Customer.Name,  Order.RedBROJ;
isn't appropriate, because list customers more than once(the query works but
the report don't - Run-time error 3612 Multi-level GROUP BY clause is not
allowed in a subquery) and without Order.RedBROJ in the GROUP BY there are
no success. 
Can anyone shed any light please?

TIA,

    Ervin

-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://databaseadvisors.com/pipermail/accessd/attachments/20030723/b720a7f9/attachment-0001.html>