I'm very new to this and would appreciate some help
I have 2 tables A and B
A table contains: ID, Starting Station, Scheduled Departure Time, Final Station, Scheduled Arrival Time
B Table contains records of vehicle with time stamp at certain stops for 24 hour period like this:
Vehicle ID, Direction, Stop ID, Time
I would like to make a query or a write a procedure or macro for resulting table to show vehicle's times through stops (Stops in columns) and ID vehicles in rows in ascending order by Dep Time like:
Row 1: ID, DepTime, stop 1, stop2 stop3 etc to end stop, Arr Time
RoW 2: ID, DepTime, stop 1, stop2 stop3 etc to end stop, Arr Time
Etc.....
Pls Note NOT All Stops have time recorded in Table B (some could be missing as GPS is not perfect )so the procedure should be able to add an empty or null value in the column if no adequate time was found
Also recorded time values for end stops may actually exceed scheduled arrival time (I guess 15min late should be allowed for)
Thanking anyone pointing me in the right direction