[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/dcf9d450/attachment.html>