Solved How to track odometer per trip and total... (1 Viewer)

[Garren.Shannon]

Hey all,

Several folks have noted that my table design is incorrect and I now see why but I can't think of a better way to design it. Here is what I have.

Vehicle table - ID, VehicleName, CurrentOD

Trip Table - ID, Vehicle, Depart-OD, Return-OD

I need to track current mileage per bus. I also need to track start and finish OD per trip. Bus drivers report their trips at the end of the day.

What is the best way to keep the Vehicle CurrentOD up-to-date? I am thinking that simply getting the difference between trip starting and ending OD and ADDING that to the Vehicle current OD would work.

Any better ideas?

 

[theDBguy]

The "best-practice" approach is if you can calculate the value, then you don't store it. Instead, you simply calculate it every time you need it. This makes sure you are always working with accurate data. However, it is your database, and you are the one who will have to deal with it, so you are free to go with whichever approach is most comfortable with you.

 

[MajP]

If you are logging both the start and stop odometer than the calculations are much easier.
If not see discussion

Denormalized database: update adjacent records

Hi gentlemens, I suppose you will kill me for question regarding denormalized DB, but only you can help me. I have table where I store Car log book: id Date car_id Odometer (absolute value) 1 12.10.2022 1 10 000 etc Now, as is probably evident, I store odometer values as absolute...

www.access-programmers.co.uk

I do not understand this statement

What is the best way to keep the Vehicle CurrentOD up-to-date? I am thinking that simply getting the difference between trip starting and ending OD and ADDING that to the Vehicle current OD would work.

I would think the current odometer is the last odometer reading. Why would you add anything to something else?

 

[Garren.Shannon]

Can you expand on that a bit more DBGuy? Each trip record would have the start and stop OD from which I can get # miles driven for that trip. I could calculate the total differences for bus 1 for a period of X days.

If you are logging both the start and stop odometer than the calculations are much easier.
If not see discussion

Denormalized database: update adjacent records

Hi gentlemens, I suppose you will kill me for question regarding denormalized DB, but only you can help me. I have table where I store Car log book: id Date car_id Odometer (absolute value) 1 12.10.2022 1 10 000 etc Now, as is probably evident, I store odometer values as absolute...

www.access-programmers.co.uk

I do not understand this statement

I would think the current odometer is the last odometer reading. Why would you add anything to something else?

It is the last OD reading technically. But if driver 1 puts his OD entry in AFTER driver 2, then he/she overwrites the last OD with one recorded (on paper) an earlier one. ... I am not articulating this well.

But I really do appreciate having a bigger brain to bounce these questions off of.

 

[Pat Hartman]

@Garren.Shannon you have two threads going on this topic and they cross so you are going to get yourself confused by the responses. I explained to you in the OTHER post how to pick up the last odometer reading for a specific bus. I also pointed out that if you store both start and stop readings, YOU need to ensure that they are not made out of order because correcting them will be a mess. I also mentioned somewhere, that when you start a new trip, you should always ensure that the last trip was closed properly to avoid issues. We don't know your business rules but I'm telling you that you need basic logic in your form's BeforeUpdate event to ensure that records are entered in order and previous trips are closed before a new one is allowed to start.

 

[Garren.Shannon]

Thanks Pat... yea... got a little carried away there splitting the thread. My thinking was one being a technical how-to and the other a mental walk of what's the best approach to recording a continuously incrementing number like an odometer.

The current business model is drivers record their daily trips on a sheet in the bus and transfer that data into spreadsheets (one per vehicle) at the end of the day. But in practice, they sometimes update at the end of a few days. With this database, we will be issued tablets, one for each driver, so they have access to the database at all times and can input data at start and end of each trip. So the database will hopefully change the business practice. It won't fix lazy... but it will help.

But going through this thread, I have come to the realization that I should calculate the trip mileage and not track the vehicle OD at all since we can simply walk out to the bus and see it.

Speaking of "BeforeUpdate"... when talking to the trans director this morning, he asked if drivers could edit records they enter... in case they make a mistake entering data. Not a fan of that. If they enter the wrong data, should they be able to go back and update a record? Totally different topic so I will close that here and do some research on this site for best practices.

Cheers...