try to get a last results query

[Olav]

I have a table like this:
id object date value
1 apples 1-1-2002 10
2 oranges 2-3-2002 12
3 apples 1-3-2003 14

I want to make a query which return the value of the last date form each
object. So for this table i want this result:
id object date value
2 oranges 2-3-2002 12
3 apples 1-3-2003 14

and not the first record because the date of that object is older then the
date from record 3 with the same object.

can anybody please help me?
thanks
Olav

 

[PC Datasheet]

Create a query that includes all the fields. Click on the Sigma (looks like
E) button on the toolbar at the top of the screen. Under Date, change Group
By to Max. The query will now give you the results you want.

BTW, Date is a reserved word and should not be used as a field name. It is
highly recommended you change that field name.

 

[John Spencer (MVP)]

One method:

SELECT T.[Id], T.[Object], T.[date], T.[Value]
FROM [YourTable] as T
WHERE T.[Date] =
(SELECT Max(T1.[Date])
FROM [YourTable] as T1
WHERE T1.[ID] = T.[Id])