Actually, no. Access cannot sum at different grouping levels in the same
query. A subquery is one way to handle the problem. Another is to do a
query that rolls up the smallest level and groups by the next level up,
then make a query of that query that sums the 2 lowest levels, and groups
by the rest... until all levels of aggregation are rolled up.
For example, let's say you have tblOrderLine, tblOrder, and tblCustomer.
First query - qryOrderSum
SELECT tblOrder.CustomerID, tblOrder.OrderID,
Sum(tblOrderLine.PiecesQty) As PiecesQtySum
FROM tblOrder
LEFT JOIN tblOrderLine On tblOrder.OrderID = tblOrderLine.OrderID
GROUP BY tblOrder.OrderID
Next query - qryCustomerOrderSum
SELECT qryOrderSum.CustomerID,
Sum(qryOrderSum.PiecesQtySum) As PiecesQtySum, Count(*) As OrderCount
FROM tblCustomer
LEFT JOIN qryOrderSum ON tblCustomer.CustomerID = qryOrderSum.CustomerID
GROUP BY tblCustomer.CustomerID
Finally - qryOverallOrderSum
SELECT Count(*) As CustomerCount,
SUM(qryCustomerOrderSum.PiecesQtySum) As PiecesQtySum,
SUM(qryCustomerOrderSum.OrderCount) As OrderCountSum
FROM qryCustomerOrderSum
On 19 Oct 2003 06:31:22 GMT, Access <asmel@devdex.com> wrote:
[color=blue]
>I made a query with some totals. One of these totals I only get when
>using a subquery. I believe it must be possible to get the resulkt in
>one query
>
>ArtNr Total #order #customer
>123789 460 20 8
>
>So i'm selling an article in 20 different orders to 8 different
>customers witrh a total of 460 pieces.
>When I make a query with sum[artNr] number[orders] and number[customers]
>the result in the customers collumn wil always be one. Only using the
>subquery gives the right result.
>Is it possible to make this query without the sub query
>
>Peters
>
>*** Sent via Developersdex
http://www.developersdex.com ***
>Don't just participate in USENET...get rewarded for it![/color]
