I have an Access database that is used to keep track of certain unique items. It is important for us to know the current and past locations where each item was kept and when the item is moved from location to location.
I have a table to keep track of this information.
table Locations:
LocationMoveID (primary key, long integer, autonumber), ItemID (long integer, the item's unique identifier), MoveDate (date, date item was moved), NewLocation (text, the item's new location)
It was only important to log this information; now, I have been asked to do something with it.
What I need to do is create a query with all of the fields from only this table and have a new field (call it LastDate, date) that would show the last date the item was in that record's particular location (for each record and if applicable).
If an item has been moved, the LastDate should be equal to the next MoveDate (in ascending order) of only that item's Location records. For where a record is the only and/or last location (max of MoveDate) for an item, the LastDate should remain empty.
Any help you could provide would be appreciated. I can think of convoluted VBA coding to do this, but I just need a simple query and I'm over thinking this. Thanks, Adam
I have a table to keep track of this information.
table Locations:
LocationMoveID (primary key, long integer, autonumber), ItemID (long integer, the item's unique identifier), MoveDate (date, date item was moved), NewLocation (text, the item's new location)
It was only important to log this information; now, I have been asked to do something with it.
What I need to do is create a query with all of the fields from only this table and have a new field (call it LastDate, date) that would show the last date the item was in that record's particular location (for each record and if applicable).
If an item has been moved, the LastDate should be equal to the next MoveDate (in ascending order) of only that item's Location records. For where a record is the only and/or last location (max of MoveDate) for an item, the LastDate should remain empty.
Any help you could provide would be appreciated. I can think of convoluted VBA coding to do this, but I just need a simple query and I'm over thinking this. Thanks, Adam