Ervin Brindza
viner at eunet.yu
Wed Jul 23 01:53:17 CDT 2003
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>