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 <as***@devdex.com> wrote:
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!
