Hi. Long time lurker, first time poster...



I have a table of inventory picking data that lists when people removed products from various storage locations. The fields are PickID (unique), PickDateTime, UserID, and Location. The data is typically sorted by PickID, but the people do not necessarily visit locations in this order. Sorting by UserID and PickDateTime would give the actual sequence of each person.

I need to create a query that lists the travel data for each person moving between locations (i.e., from Location A to Location B to Location C and so on). The resulting fields would be UserID, FromLoc, FromDateTime, ToLoc, and ToDateTime. The travel sequences need to reset each day at midnight (00:00:00).

The attached file has a sample of raw data and what it would look like in the desired query.

Any help would be greatly appreciated!

Regards,
Rob