<!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.4923.2500" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2>Ervin,</FONT></SPAN></DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff size=2>SELECT
Customer.CustID,</FONT></SPAN></DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2> Customer.Name,</FONT></SPAN></DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2>
Sum(Order.ValAmount) AS UkVred,</FONT></SPAN></DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2>
Sum(OrderDet.Quantity) AS ukkol<BR>FROM (Customer INNER JOIN [Order] ON
Customer.CustID = Order.CustID)</FONT></SPAN></DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2> INNER JOIN
OrderDet ON Order.RedBROJ = OrderDet.RedBROJ<BR>GROUP BY Customer.CustID,
Customer.Name;</FONT></SPAN></DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff size=2>Hope
this helps.</FONT></SPAN></DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=782273911-23072003><FONT face=Arial color=#0000ff
size=2>Scott</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV class=OutlookMessageHeader dir=ltr align=left><FONT face=Tahoma
size=2>-----Original Message-----<BR><B>From:</B> Ervin Brindza
[mailto:viner@eunet.yu]<BR><B>Sent:</B> Wednesday, July 23, 2003 2:53
AM<BR><B>To:</B> AccessD@databaseadvisors.com<BR><B>Subject:</B> [AccessD]
SQL: Sum from the third table<BR><BR></FONT></DIV>
<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></BLOCKQUOTE></BODY></HTML>