Analyze within group

[ironwood9 via AccessMonster.com]

I have a table that contains sales data with a customer nbr and a sales code.

Is there a way to write a query that will "group" the data by CustNbr, and if
a particular customer does not have at least $25, do not return that CustNbr
in my record set, otherwise, just show CustNbr once in my result.

 

[Michel Walsh]

SELECT CustNbr
FROM somewhere
GROUP BY CustNbr
HAVING SUM(amountOfMoney) >= 25
ORDER BY SUM(amountOfMoney) DESC





the last line,
ORDER BY SUM(amountOfMoney) DESC
is facultative but can help you to check, visually, that those CustNbr
having less than 25$ are not listed,. since the CustNbr are now ordered by
descending amount.


Vanderghast, Access MVP

 

[Jerry Whittle]

Make sure to put in the correct field and table names.

SELECT CustSales.CustNbr, Sum(CustSales.SalesCode) AS SumOfSalesCode
FROM CustSales
GROUP BY CustSales.CustNbr
HAVING Sum(CustSales.SalesCode)>25;