max date query

[wat3rfall6]

Hello everybody!
I have a table called History with these registries.

sn date location

0109199 5/15/2011 repair
0109199 6/15/2011 warehouse
0109200 7/10/2011 mast
0109200 8/10/2011 warehouse
0109201 9/5/2011 warehouse
0109201 10/5/2011 repair

I have a combobox (Cmb_Loc) to choose a location (eg warehouse) and another listbox (LocList) that should load all sn of the devices currently stored in the location I choose (the last date of every sn represents the current location of the device).

If you use max() then it takes the last date of the whole date column, while I need to use last date(max date) of every device (sn).

So far the listbox is partly working and here is the code.
SELECT History.[Mast_Name], History.SN, History.[Cmb_Loc], History.ID, Types.Type, History.Comments
FROM (Types INNER JOIN Sensors ON Types.Type = Sensors.Type) INNER JOIN History ON Sensors.[Serial Number] = History.SN
WHERE (((History.[Cmb_Loc])=[Forms]![Search_Status]![LocList]));


This code shows all devices that have been to the warehouse
0109199 6/15/2011 warehouse
0109200 8/10/2011 warehouse
0109201 9/5/2011 warehouse


While I want only these that were last there and not moved anywhere else.
Can anybody help me with the code of the query for the max date?

Thank you all in advance.

 

[pbaldy]

Does this help?

http://www.baldyweb.com/LastValue.htm

 

[wat3rfall6]

Yes indeed!Thank you so much!

 

[pbaldy]

Happy to help!