sum after max records found

[Marcie]

I have the following information in a table:
id record amount date
1001 0 100 12/31/2009
1001 1 150 12/31/2009
1001 0 100 11/31/2009
1001 1 150 11/31/2009
1002 0 75 12/31/2009
1002 0 125 11/31/2009
I want the max date by id and record and sum amount so i want
1001 250 12/31/2009
1002 75 12/31/2009
Right now I am doing it in 2 queries I first find max then second query sums
it because if I try to sum it in the first one I actually get all amounts

 

[John Spencer]

I'm not sure this will work for you, but you can try using a correlated
subquery in the where clause.

SELECT ID, Sum(Amount) as Total, [Date]
FROM [SomeTable]
WHERE [Date] =
(SELECT Max(Temp.[Date])
FROM [SomeTable] as Temp
WHERE Temp.ID = [SomeTable].ID)
GROUP BY ID, [Date]

Another method might be to use the following
SELECT ID, Sum(Amount) as Total, [Date]
FROM SomeTable INNER JOIN
(SELECT ID, Max(SomeTable.Date) as LastDate
FROM SomeTable
GROUP BY ID) as TEMP
ON SomeTable.ID = Temp.ID
AND SomeTable.Date = TEMP.LastDate
GROUP BY ID, SomeTable.Date

By the way, Date is not a good choice for a field name. It can easily become
confused with the Date() function which returns the current date. It is much
better if you can be a bit more descriptive and use something like
TransactionDate, SaleDate, or even TheDate will suffice.

If you don't know how to build a query using the SQL window, you can post back
and I (or someone) will try to lead you through building the first query in
query design view.

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