<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=windows-1252">
<META content="MSHTML 5.50.4134.600" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Hi,</FONT></DIV>
<DIV><FONT face=Arial size=2>I'm struggling with a simple SQL
expression, hope someone can help:</FONT></DIV>
<DIV><FONT face=Arial size=2>There are 2 tables: </FONT></DIV>
<DIV><FONT face=Arial size=2>- Order(fields: RedBROJ(Autonumber), CustId,
ValAmount) - RedBROJ is a join field</FONT></DIV>
<DIV><FONT face=Arial size=2>- Customer(CustId, Name) </FONT></DIV>
<DIV><FONT face=Arial size=2>and OrderDet(RedBROJ, quantity)</FONT></DIV>
<DIV><FONT face=Arial size=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2>But the following:</FONT></DIV>
<DIV><FONT face=Arial size=2>SELECT Order.CustId, Customer.Name,
Sum([Order].[ValAmount]) AS UkVred, (select sum(quantity) from OrderDet
WHERE OrderDet.RedBROJ=Order.RedBROJ) AS ukkol<BR>FROM Customer INNER JOIN Order
ON Customer.CustId= Order.CustId<BR>GROUP BY Order.CustId, Customer.Name,
Order.RedBROJ;</FONT></DIV>
<DIV><FONT face=Arial size=2>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. </FONT></DIV>
<DIV><FONT face=Arial size=2><FONT size=3>
<P>Can anyone shed any light</FONT> please?</P>
<P>TIA,</P>
<P> Ervin</FONT></P></DIV></BODY></HTML>