Omit groups of records that don't meet criteria

T

thejohnsonclan

I have data that is sorted by date with dollar amounts. I need to sum the
dollar amounts by date, and if it doesn't meet a threshold, omit the records,
otherwise I want to display the record sand the total.
 
J

John Spencer (MVP)

Wow, lots of detail there. You might try something like the following

SELECT A.*
, (SELECT Sum(DollarField)
FROM SomeTable as C
WHERE C.DateField = A.DateField) as TotalForDate
FROM SomeTable as A
WHERE DateField in
(SELECT DateField
FROM SomeTable as B
GROUP BY DateField
HAVING Sum(DollarField) > 250)

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
D

Dale Fye

Or, given the limited information, you might try:

SELECT [DateField], Sum([DollarField]) as Sum
FROM yourTable
GROUP BY [DateField]
HAVING Sum([DollarField]) > 500

HTH
Dale
 
Top