By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,389 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,389 IT Pros & Developers. It's quick & easy.

Extra SUM in query

P: n/a
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!
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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!


Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.