I think I'm beginning to understand the structure that should work.
I believe the key is when a vehicle crosses a state line, and the key question is which direction is the vehicle travelling in? Is it entering or leaving a particular state?
Now you can derive the direction the vehicle is taking from the paper TripSheet that you already have, and indeed the MS Access version of this TripSheet would allow you to find information from a previous entry, however in my experience this is to be avoided where possible as it could lead to errors.
The other source of error I can foresee is when a new driver takes over a vehicle, he may not know where the vehicle last travelled from, so now he has to make a guess or possibly make an incorrect entry.
Taking into account these possible sources of error and finding a way to eliminate them I'm thinking your structure should look something like this:
From:.... To:...... Odemetre Reading:...
I have no idea which state links to which state, so for the purpose of testing this idea let's assume we have the uniform size and shape states, all being hexagonal.
We will start off with state "A" now imagine adding more states around this hexagon representing state"A". Start at the 12 O'clock position above state "A" and add the state "B" and in a clockwise direction add the state's C,D,E,F,G, now you have a representation showing seven states with state "A" in the centre.
Now add another state on top of state "B" in the 12 O'clock position again. Call it state "H" and if you want you can add the rest of the state's in a clockwise direction.
Let's assume that the vehicle is loaded at the depot which for the purpose of this exercise is at the exact centre of state "A"
So now we can begin to fill our table:
From:.... To:...... Ode-metre Reading:...
The driver arrives at the vehicle, he does not know the vehicles previous history so he fills out the tables thus:
From:.... To:...... Ode-metre Reading:...
"A"........"A".........2003057
And also for the purposes of this exercise let's assume the destination is the exact centre of state "H" the driver needs to travel vertically through state "B" to get to state "H".
So the driver needs to record the mileage when leaving state "A" and entering state "B"
From:.... To:...... Ode-metre Reading:...
"A"........"A".........2003057
"A"........"B".........2003557
The driver travels through state "B" and eventually comes to the border with state "H" now the driver will need to record the following:
From:.... To:...... Ode-metre Reading:...
"A"........"A".........2003057
"A"........"B".........2003557
"B"........"H".........2004235
Finally the driver arrives at his destination the job is finished so he finishes off the sheets thus:
From:.... To:...... Odemetre Reading:...
"A"........"A".........2003057
"A"........"B".........2003557
"B"........"H".........2004235
"H"........"H".........2004704
With this design the data itself informs you of the start and finish readings without having to record that information somewhere else. The only thing is with an MS Access table you might expect to enter the data in that order as shown above and that the table would maintain that order for you, however this is not the case! As a general rule never assume anything will be stored in the way you expect or want it to, arrange to have your own control over it. In this case you want to add an extra field called an ID Field and have it as an auto-number incrementing by one, so your table above now becomes:
ID:......From:.... To:...... Odemetre Reading:...
1........"A"........"A".........2003057
2........"A"........"B".........2003557
3........"B"........"H".........2004235
4........"H"........"H".........2004704
You may also want to add fields to record the TripSheet number and also the date, however it is not normal to record this sort of detail at this level, you would record this type of information in a master or if you like a "parent" table.
The 1st line on the Main section= When the driver turns on the truck, wherever he is at, he will note down the location he is currently at and odometer reading.
He will finish his run and his last location (where he drops the truck off and goes home) will be noted on the Unloading Section.
Based on the PDF... that is one run. So the start of his journey was A-19637 (that was when bearly got in the car, turned the ignition on and noted the location/odometer). The rest of the lines (B,C,D etc) on that section are state line crossings. The last line on the Unloading Section would be the end of the drivers journey. In this case it is H- 20365
2nd trip New Driver
Now, lets say a new driver takes over the truck the next day. He will turn on the truck, and note down the location he is at as well as the Odometer reading ( which will match the same odometer the last driver noted- 20364) and he will place that on the first line of the Main section.
This is what I understood...It looks as if it could work. Thinking it over..This is based on the PDF as an example
ID....From..............To.........Odometer.........Miles Driven ( Calculated Field)
1.........Utah............ Utah............ 19637........[TripSheetMainOdometer2] - [TripSheetMainOdometer1] reprsents Utah miles
2.........Utah..............Arizona........ 19952........[TripSheetMainOdometer3] - [TripSheetMainOdometer2] represents Arizona miles
3.........Arizona..........Nevada........19982........[TripSheetMainOdometer4] - [TripSheetMainOdometer3] represents Nevada miles
4.........Nevada..........CA ..............20107.......[TripSheetMainOdometer5] - [TripSheetMainOdometer4] represents CA miles
5........CA ................CA .............20365
Based on PDF example
Total Miles Driven each state
Utah: 315 (19952-19637)
Arizona=:30 (19982-19952)
Nevada : 125 (20107-19982)
CA : 258 (20365-20107)
