born2gamble
Registered User.
- Local time
- Yesterday, 19:33
- Joined
- Aug 16, 2011
- Messages
- 31
I assume I would need to create one table per each truck.
ID....Date....Truck....State....Odometer.....Miles Driven
1.....Date....TruckA...UT.......500..........row id2 -id1
2.....Date....TruckA...AZ.......1000.........row id3- id2
3.....Date....TruckA...NV.......1,700........etc
Is it possible to have the Miles Driven field automatically do this for every new record, in that order?
I could simply filter out by Date/State and sum up the column Miles Driven.
Your right the other information are just red herrings to this issue. It is information that will be inputted into the system either way, and I was trying to make it work so information was only inputted once (trip sheet) and I would be able to extract the necessary information I needed to do IFTA (Miles Driven in each state per each truck quarterly)
Not sure if you have Access 2010 or not, so I can send an updated db.I have attached a jpg so you could see what I mean.
It is basically
ID.Truck...Date.....Odometer1.....Odometer2.....Odometer3......State1.....State2......State3......TotalMilesState1.......TotalMilesState2.........TotalMilesState3
1. TruckA..1/2/2012..500...........1200..........1,500..........UT.........AZ..........NV..........(odometer2-odometer1)..(odometer3-odoemeter2)..etc
2.
Seems like really bad design on my part as I am trying to somehow query all the Utah miles driven, but am forced to query State1 and MilesMainstate1, filter by state (CA), write that number down, then run a query on State2 and MilesMainState2, filter by CA, write the number down.. and at the end, add them all up.
ID....Date....Truck....State....Odometer.....Miles Driven
1.....Date....TruckA...UT.......500..........row id2 -id1
2.....Date....TruckA...AZ.......1000.........row id3- id2
3.....Date....TruckA...NV.......1,700........etc
Is it possible to have the Miles Driven field automatically do this for every new record, in that order?
I could simply filter out by Date/State and sum up the column Miles Driven.
Your right the other information are just red herrings to this issue. It is information that will be inputted into the system either way, and I was trying to make it work so information was only inputted once (trip sheet) and I would be able to extract the necessary information I needed to do IFTA (Miles Driven in each state per each truck quarterly)
Not sure if you have Access 2010 or not, so I can send an updated db.I have attached a jpg so you could see what I mean.
It is basically
ID.Truck...Date.....Odometer1.....Odometer2.....Odometer3......State1.....State2......State3......TotalMilesState1.......TotalMilesState2.........TotalMilesState3
1. TruckA..1/2/2012..500...........1200..........1,500..........UT.........AZ..........NV..........(odometer2-odometer1)..(odometer3-odoemeter2)..etc
2.
Seems like really bad design on my part as I am trying to somehow query all the Utah miles driven, but am forced to query State1 and MilesMainstate1, filter by state (CA), write that number down, then run a query on State2 and MilesMainState2, filter by CA, write the number down.. and at the end, add them all up.
