Help!!! with table access design-Calculated Fields (1 Viewer)

[born2gamble]

Hi..having trouble with how to attack this.
Attached a pdf (showing how the form will look) and my access table is attached.
What I need at the end of the day is for me to be able to just look at how much total miles were driven in each state. This pdf represents 1 trip, and there will be many many trips. Every three months I will have to run a query to see the total miles driven in each state.

I can have TripsheetTotalMile1 = TripSheetMainOdometer2-TripSheetMainOdometer1
But I am completely lost, since TripsheetTotalMile1 wouldnt be attached to any specific state.

The order of States will also vary....it wont always be UT,AZ,NV,CA etc
So far doing this by hand has been very VERY tedious!

 

[Uncle Gizmo]

I think the PDF showing how the form will look is very misleading and inaccurate. It reads as if the driver has had to record the mileage reading when he enters a specific town, this will never happen! It is more likely that the driver will record the mileage when loading, unloading or refueling. Please acknowledge if my summary is correct or if it is incorrect please explain further.

 

[born2gamble]

I think the PDF showing how the form will look is very misleading and inaccurate. It reads as if the driver has had to record the mileage reading when he enters a specific town, this will never happen! It is more likely that the driver will record the mileage when loading, unloading or refueling. Please acknowledge if my summary is correct or if it is incorrect please explain further.

That is correct, the driver will record the mileage when he reaches a different state. Please disregard the Loading section and down for now. I will be able to figure that out based on how to attack the top portion.

I named the top portion TripSheetMainOdometer1 2 3 4 etc and TripSheetMainTotalMiles1 2 3 4 etc

 

[Uncle Gizmo]

I will be able to figure that out based on how to attack the top portion.

No that's not a good idea you need to figure it out now.

So you need to decide when the driver is going to record the mileage, will the driver record the mileage when crossing a state line, loading, unloading and refueling? Are there any more instances when the driver will record the mileage? For instance when taking a statutory break?

What instance denotes the start and the end of the journey? In other words it is important to know when a new journey starts and when a journey ends, in other words where do you set the recorded mileage back to zero? When do you decide the maximum mileage has been reached?

 

[born2gamble]

I will be able to figure that out based on how to attack the top portion.

No that's not a good idea you need to figure it out now.

So you need to decide when the driver is going to record the mileage, will the driver record the mileage when crossing a state line, loading, unloading and refueling? Are there any more instances when the driver will record the mileage? For instance when taking a statutory break?

What instance denotes the start and the end of the journey? In other words it is important to know when a new journey starts and when a journey ends, in other words where do you set the recorded mileage back to zero? When do you decide the maximum mileage has been reached?

Your right. Well for IFTA purposes, I need to know how many total miles were driven in each state. Yes, the driver will note down his mileage while crossing a state line and note that at the top where you see the majority of the odometer entries.They will also note the mileage when loading/unloading, but that is more for payroll not for IFTA.

On the Pdf I wrote some letters to give an idea at what I need
A - 19637 UT mileage driven in UT= B-A
B- 19952 AZ mileage driven in AZ= C-B
C- 19982 NV mileage driven in NV= D-C
D- 20107 CA mileage driven in CA= H-D
H- 20365 CA This is the final stop ( Unloading section)

I will need it to get the LAST odometer entry in the Unloading section (TripSheetUnLoadingOdometer etc), and minus(-) the LAST odometer entry in the main section (TripSheetMainOdometer etc). In this example its H-D

After all is said and done, I need to be able to filter this or query all Utah total miles driven etc every 3months.

 

[Uncle Gizmo]

What instance denotes the start and the end of the journey?

It's still not clear to me how you decide when a journey starts and when it ends? Please could you clarify.

 

[Uncle Gizmo]

Thinking............... It may not be necessary to identify the start and end, if it is not necessary then please explain why it's not necessary.

 

[born2gamble]

What instance denotes the start and the end of the journey?

It's still not clear to me how you decide when a journey starts and when it ends? Please could you clarify.

The Start of his journey will always be the 1st line of the Main Section which in this case is (A) 19637 on the odometer

The end of his journey will always be the last line of the Unloading Section, in this case its (H) 20365 on the odometer

 

[Uncle Gizmo]

Will the trip have more than one driver? Will the trip have a situation where one driver sleeps and they changeover at intervals? Again is this information necessary for this task? If not why not?

 

[born2gamble]

Will the trip have more than one driver? Will the trip have a situation where one driver sleeps and they changeover at intervals? Again is this information necessary for this task? If not why not?

It is not necessary for IFTA purposes. They just want the total miles driven in each state per each truck ( TripSheetTractor) every 3 months.

 

[Uncle Gizmo]

So what you're looking for is mileage covered in each state in a three month "quarterly" period.

So for example the period might be from the 1st of January, 2012 to the 31st of March, 2012.

 

[Uncle Gizmo]

Doesn't look to me like there is enough room on those sheets to record all of the way points I would expect. Will there be more than one sheet? Does the driver fill out one sheet per day, per week, per month,?

 

[born2gamble]

So what you're looking for is mileage covered in each state in a three month "quarterly" period.

So for example the period might be from the 1st of January, 2012 to the 31st of March, 2012.

Yes to the first question, but also filtered by each truck. So the query would pull Truck1 CA = 1,500 miles driven Truck 1 NV = 800 miles driven Truck2 CA = 1400 miles driven etc. Its not visible on the pdf, but i will be inputting the Truck number in access. (TripSheetTractor)

Each Sheet represents each completed load.
It depends how often he will fill one sheet out based on where his destination is.
For example, if the driver is going from los angeles,CA to Miami,FL...it will take him about 4 days to get there ( One Sheet). Then when he goes from Florida back to CA...that will be a different sheet. so in 8-10 days he has filled out only 2 sheets.

example 2:
Driver goes from CA to Nevada in one day ( one sheet)
Drive goes from Nevada back to CA in one day ( another sheet)

I hope you understand my examples.
Also you are correct, I should add more waypoint fields in the main section since if the driver does go from CA to NY, he will cross more than the alloted 7 fields I have for state lines on the pdf/access table.

 

[Uncle Gizmo]

What happens in a split load situation?

When there is a part load delivered to 2 or more customers?

 

[Uncle Gizmo]

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.

 

[Uncle Gizmo]

ID:......From:.... To:...... Odemetre Reading:...
1........"A"........"A".........2003057
2........"A"........"B".........2003557
3........"B"........"H".........2004235
4........"H"........"H".........2004704

I'm not happy with this result ..... It needs some more thought.... I think it will be possible to derive a simpler rearrangement..... don't know.... thinking....

 

[born2gamble]

What happens in a split load situation?

When there is a part load delivered to 2 or more customers?

When there is a part load delivered to 2 or more customers?[/QUOTE]
Its still one trip sheet. For example.

A customer may call, and say, ok i need you to pick this up and deliver part of it to LocationA and part of it to LocationB. So on the trip sheet, the driver will place that information on the Unloading Section.
We look at that as still one trip. That is the reason there are 3 Loading(pickup locations) and 4 Unloading sections (drop off location) in each trip sheet.

 

[born2gamble]

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)

 

[Uncle Gizmo]

2nd trip New Driver.....

.......which will match the same odometer the last driver noted- 20364) and he will place that on the first line of the Main section.

The interesting observation, although I think it might be a misleading piece of information.

What I mean is half the battle is deciding which information is relevant and which is not.


Let's ponder the problem again, all you need is to know how many miles an individual vehicle did in each particular state for a given three month period.

So for the purposes of answering this question above, then each individual "trip" and each individual "TripSheet" the drivers' names, all of this other associated information are just red herrings.

This extra information is necessary to the task of making sure that the drivers' collect the relevant information for you.

The only task required of the database is to calculate the mileage travelled in each state, group this data by "State" and by the specified interval and then produce the sum Total.

Looking at the final table structure take the "To:" column, basically this is either set to "A" or "B" or "C" in other words whichever state that the particular mileage reading was recorded in.

So in my final example table let's ignore the "From: column" we can now imagine an extra field on the end called "mileage" so for line item ID "1" the state is "A" the metre reading is 2003057 look ahead to the next record, record ID "2" in the "To:" column we have a "B" so that would indicate we have moved out of the state "A" into the state "B" so now we can take be mileage reading which at row ID "2" which is 2003557 and subtract the previous reading in row ID "1" and we have the resulting mileage of: 500.

This is what we want, however how do we know to choose ID "1"? In this simple example it's easy it's the first record, but the record you actually want to find is the first record of a given three month period. Let's say the three month period begins on the 1st of April, 2012, so how do we know which record to choose in this case?

We can only go by the date, the cutoff point, midnight on the 31st of March 2012. However there is a small issue, the vehicle may be travelling at this time, so we may have to make a rule to take this into account, we can assume that because the trip started in march, and ends in April that all of the mileage recorded should be allocated to the previous period, the period ending on the 31st of March.

 

[Uncle Gizmo]

You will also need a way to calculate the mileage, I believe this sort of calculation is usually referred to as "running sum" or "running balance" you can find information on this, here on Roger's website, Roger's Access Library here: http://www.rogersaccesslibrary.com/forum/topic279.html