Display Another Record from Same Table in a Query

[torttion]

Hello,

I have DB where I track my company's equipment. In the DB, I have a table that tracks the equipment's movements. Each time a piece of equipment moves from one project to another an entry is made where the following fields are populated:

-Move_ID (Primary Key - Autonumber)
-Equipment_Number
-Pickup_Date
-Pickup_Location
-Delivery_Date
-Delivered_Location

I'm trying to create a query that will display the beginning and end dates that a piece of equipment was on a project.


Project = Delivered_Location
Beginning Date = Delivered_Date


End Date = Pickup_Date (from the next record where the equipment number is the same and the Pickup_Date is greater than and closest to the Delivered_Date)

I'm relatively new to access and my SQL skills rate at about a .001 out of 10 so I'm using the query design functionality.

Any help is greatly appreciated.

[John_G]

I don't think this is the right approach. How do you account for cases where a piece of equipment is picked up from one project, but not delivered to another one because it isn't needed anywhere yet, or needs repair, or some other reason. In cases such as that, there will be no "next record" to look at. My suggestion would be to explicitly update the "pickup_Date" field when a piece of equipment is removed from a project, whether or not it is moved to another one.

[torttion]

Hi John_G: The locations actually tie to a Projects tables because a project could have more than one location. However, you're right, there are cases where a piece of equipment could move to a location that isn't part of a project, like needing a repair or simply returning to our main yard. In which case, these locations belong to project "Repair", "Yard", or "Other". So, anytime a piece of equipment moves, it does get a Pickup_Date regardless of whether its going to a project or some other location.