Query and Calculating Pennultimate Dates (1 Viewer)

[amp]

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

 

[jzwp22]

It sounds like you need to nest a query within your query. Something like this perhaps

SELECT Locations.LocationMoveID, Locations.ItemID, Locations.MoveDate, Locations.NewLocation, (SELECT Top 1 Q1.movedate FROM Locations as Q1 WHERE Q1.movedate<locations.movedate and Q1.itemID=locations.itemID ORDER by ItemID, movedate desc) as LastMoveDate
FROM Locations
ORDER BY ITEMID, MoveDate desc

 

[amp]

It sounds like you need to nest a query within your query. Something like this perhaps

SELECT Locations.LocationMoveID, Locations.ItemID, Locations.MoveDate, Locations.NewLocation, (SELECT Top 1 Q1.movedate FROM Locations as Q1 WHERE Q1.movedate<locations.movedate and Q1.itemID=locations.itemID ORDER by ItemID, movedate desc) as LastMoveDate
FROM Locations
ORDER BY ITEMID, MoveDate desc

Thanks! I've got it going with only a few changes. The subquery was almost exactly spot on.

I was confusing in the way I explained what I needed; the dates on the record should basically be the start (movedate) and the end date for when the item was in a particular location.

I also found this great article:
http://allenbrowne.com/subquery-01.html#AnotherRecord

Thanks for your help!

 

[jzwp22]

Glad you found a solution! Good luck with your project.