Hello,
I have a table with the following fields:
Trip ID (Short Text) - all unique values, for example TRIP1, TRIP2 etc.
Trip Start Time (Date/Time) - dd/mm/yyyy hh:mm:ss format
Trip End Time (Date/Time) - dd/mm/yyyy hh:mm:ss format
Vehicle Type (Short Text) - For example CAR, TRUCK, BUS etc
For instance:
TripID Start Time End Time Veh Type
TRIP1 01/01/2017 3:00:00AM 01/01/2007 6:00:00AM CAR
TRIP2 01/01/2017 5:00:00AM 01/01/2007 9:00:00AM CAR
TRIP3 01/01/2017 7:00:00AM 01/01/2007 9:00:00AM CAR
TRIP4 01/01/2017 2:00:00AM 01/01/2007 9:00:00AM BUS
TRIP5 01/01/2017 5:00:00AM 01/01/2007 9:00:00AM BUS
TRIP6 01/01/2017 8:00:00AM 01/01/2007 9:00:00AM BUS
TRIP7 01/01/2017 2:00:00AM 01/01/2007 3:00:00AM TRUCK
TRIP8 01/01/2017 4:00:00AM 01/01/2007 5:00:00AM TRUCK
TRIP9 01/01/2017 6:00:00AM 01/01/2007 7:00:00AM TRUCK
(Normally I will have many thousands of trips but this serves our purpose here)
What I need to be able to do, is assign each trip a unique vehicle identifier, based on the start and end times and vehicle type.
So in the example above:
TRIP1 could be assigned to "CAR1", TRIP2 begins before TRIP1 ends, so it would be assigned to "CAR2", BUT, because CAR1 has completed its first task by 6:00:00AM it is now available to perform TRIP3 which commences at 7:00:00AM.
TRIP4 would be assigned to "BUS1", which is unable to perform any other BUS trips, thus TRIP5 would be assigned to "BUS2", which also is unable to perform any other BUS trips, so TRIP6 would be assigned to "BUS3".
TRIP7 would be assigned to "TRUCK1", which finishes in time for TRIP8 to be performed by "TRUCK1", which finishes in time for TRIP9 to also be performed by "TRUCK1".
So the final output would be:
TripID Start Time End Time Veh Type Veh ID
TRIP1 01/01/2017 3:00:00AM 01/01/2007 6:00:00AM CAR CAR1
TRIP2 01/01/2017 5:00:00AM 01/01/2007 9:00:00AM CAR CAR2
TRIP3 01/01/2017 7:00:00AM 01/01/2007 9:00:00AM CAR CAR1
TRIP4 01/01/2017 2:00:00AM 01/01/2007 9:00:00AM BUS BUS1
TRIP5 01/01/2017 5:00:00AM 01/01/2007 9:00:00AM BUS BUS2
TRIP6 01/01/2017 8:00:00AM 01/01/2007 9:00:00AM BUS BUS3
TRIP7 01/01/2017 2:00:00AM 01/01/2007 3:00:00AM TRUCK TRUCK1
TRIP8 01/01/2017 4:00:00AM 01/01/2007 5:00:00AM TRUCK TRUCK1
TRIP9 01/01/2017 6:00:00AM 01/01/2007 7:00:00AM TRUCK TRUCK1
I have a way of doing this in excel that takes a long, long time, but is nonetheless accurate. I'm hoping there is a way that MS Access can perform this task relatively quickly as I need to devote this time to other things.
Can anyone please help?
Thanks so much in advance,
JD