Last Records

[Scott]

I need to query the last records for each date in a table. The table data
has three fields - Id (Auto Number), Date and Amount. Each Date field can
have one or several records. Example data:
1,7/1/07,5
2,7/1/07,9
3,7/2/07,2
4,7/3/07,4
5,7/3/07,10
6,7/3/07,6
7,7/3/07,3
I need to query the last record (determined by the Id field) for each date.
The Id field may or may not be included in the results. The query should
return:
7/1/07,9
7/2/07,2
7/3/07,3
How do I do this?

Thanks,
Scott

 

[Jerry Whittle]

SELECT TblScott.Date, TblScott.Amount
FROM TblScott
WHERE TblScott.ID In (SELECT Max(TblScott.ID)
FROM TblScott
GROUP BY TblScott.Date);

 

[mray29]

One way is to set up 2 queries.

Call the first one qryMaxID:

SELECT Max(Table2.ID) AS MaxOfID
FROM Table2
GROUP BY Table2.dtDate;

Then do another query, usign qryMaxID and Table2 (or whatever the name of
your table is) and join it on ID:
SELECT Table2.dtDate, Table2.Qty
FROM Table2 INNER JOIN qrymax ON Table2.ID = qrymax.MaxOfID;

This should produce the results you want.