History Table - Current Location Query

[Enigma007]

Heya.

I have a database that I use to keep track of inventory. I am tracking the current location of each item in a history table. The table has a unique autonumber id, a reference to the item, a reference to the location the item now resides, and a date that the item was put in the location.

History table looks like this:
ID
Item
Location
Date

Now, I can see the progression of an item easilly, but I am looking to create a query that gives me the most recent location of every item in the history table.

If I create a query with Max(Date) and group on Item, I get the last date that every item was moved, but I need to get the date, the location, the id and the item.

Unfortunately, as would be expected, I can't group on id or location because that would then give me a different set of data.

How can I go about getting this information out of a query?

Any help would be greatly appreciated. Thanks!

 

[PHV]

Something like this ?
SELECT A.* FROM tblHistory A INNER JOIN
(SELECT Item, Max([Date]) As MaxDate FROM tblHistory GROUP BY Item) B
ON (A.Item=B.Item) AND (A.Date=B.MaxDate);
BTW, try to avoid reserved words like Date for field name.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244