History Table - Query to find Current Locations

[Enigma007]

I just realized that I posted in the wrong forum initially(Access Forms), and have now posted here. Please don't kill me for double posting! It was an accident. In any event.

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 all together.

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? I have experiemented and been successful by combining 2 queries, but I am hoping to find a more intuitive way.

Any help would be greatly appreciated. Thanks!

 

[lespaul]

This should get it for you (replace TABLE with your table name):

SELECT * FROM TABLE T1 WHERE T1.[DATE] = (SELECT MAX([DATE]) FROM TABLE T2 WHERE T1.ITEM = T2.ITEM)

Leslie

 

[Enigma007]

I'm not sure I understand completely. I have my location table as T1. What would T2 be in this case?

 

[Enigma007]

Er... Nevermind. You made it even easier for me by just having to substitue my Table name. I am just an idiot trying to make it harder for myself

Works perfectly!

THanks!

 

[lespaul]

Glad you figured out the easy part!

;o)

leslie