Weird Question - Access Developer Forum


	Back to Access Developer Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Weird Question

Andrea Buckridge

4 months ago

I've built a pretty good database to track my journey as a truck driver. It tracks many things such as customers, loads, mileages, when things expire (license, endorsements, twic card, dot physical, trucks, trailers, maintenance for both, when inspections are due, when preventative maintenance is due, all maintenance on both truck and trailer, how many miles I've driven since I started keeping track, daily mileages, miles per load, miles per stop, assigned miles, and how far I've gone above the assigned miles, and much, much more. There are at least 8 different places that I record mileages in my database. I didn't set up a table for mileages, and I'm beginning to wonder if I should have. If, for example, I stop at a shop to get both the truck and trailer repaired or inspected and parked for the night at the same time, the mileage would be recorded in 5 places (stop, truck maintenance, trailer maintenance, Post trip, and pre-trip). This has been making me wonder if I should have done it differently.

	Ray White @Reply 4 months ago
Hey Andrea I have been building Trucking Accounting software for over 20 years. You can look at mine and maybe it will give you some ideas. Truckers Profit Tracker

	Ray White @Reply 4 months ago
It was Trucking Pro Software

	Andrea Buckridge OP @Reply 4 months ago
Thank you. That doesn't however answer my question. Should I have used a separate table to track the mileages and related that table to all the different tables that use those mileages?

Andrea Buckridge OP @Reply

4 months ago

I'm a company driver, so I'm not interested in tracking profits and expenses. Most of the apps I could find were focused on profits, having been built with owner operators in mind. Some were just for tracking loads, but they wouldn't allow me to enter more than one load number (sometimes necessary in my line of work) or just allowed one shipper and receiver (sometimes multiples were required). And none of them would track preventative maintenance or when licenses expire, or twic card, or dot physical. Therefore, I built one that will do all that and more. I'm going to be adding to it too, to track when annual permits expire, I think.

Richard Rost @Reply

4 months ago

In general, you want one authoritative place where a fact is stored. Mileage is a fact, not an attribute of maintenance, trips, or stops. The clean design is usually a central Mileage or Odometer table that records date, vehicle or trailer, start and end miles, and context. Your other tables should reference that record instead of storing the same mileage values again. What you have now works, but repeating the same mileage in multiple tables increases the risk of inconsistencies later. If you ever have to correct a mileage entry, you do not want to fix it in five places. A single mileage table with relationships keeps the data cleaner and easier to maintain long term.

Maybe this database will give you some ideas: Vehicle Maintenance

	Ray White @Reply 4 months ago
Yes I would do that. It would make it more manageable for you.

Thomas Gonder @Reply

4 months ago

Yes, it's the same idea as tracking time spent on work tasks (well, I guess it could be all of life's activities). One table that has a start and stop time, the client and/or customer the work was done for and the specfic work that was done.

I worked at SMI (Dealer Software) years ago, on a 50-state payroll portion of a comprehensive software package. I was taken aback when the dealer told me each mechanic gets different rates depending on warranty, company or independant for a given manufacture rig and what mechanic job they were doing. I had to integrate all that info into paying the mechanics, including some weird overtime/holiday schedules.

I'm guessing if your tracking your time, then you'll need a way to integrate that to the milage, as well as all the other tables. It's not too difficult, it just takes LOTS of planning. And then there's the exceptions that really test your understanding of the model you're building.

	Andrea Buckridge OP @Reply 4 months ago
Thank you all so much. I appreciate the answers. It began dawning on me that I should have set it up that way. Not sure how to change all that over though....at this point. It's definitely something I'll have to be careful with and plan out.

	Andrea Buckridge OP @Reply 4 months ago
Richard Wow! I'm watching your Cascading combo boxes video right now. I paused it to tell you that (before becoming a member here at Computer learning zone) I had already figured out how to build my database to automatically populate the city and state when i enter a zip code! With around 800 customers so far, I am so thankful that's a possibility!

	Andrea Buckridge OP @Reply 4 months ago
I appreciate the reference to the Vehicle Maintenance video and cascading combo boxes! I'm learning well. I'll have to watch the videos on how to use the update queries. And figure out how to build a Mileage table into my database.

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Developer Forum.

Next Unseen

New Feature: Comment Live View