Suggestions for a report?

[GinaStar]

I need some advice please? I'm currently setting up a new maintenance database for the office - currently we use an excel spreadsheet to track grease/oil, etc - it's laid out like this:

truck Blank OilDate Miles due Current TransDate Miles Due OverDat Miles Due Svc ACF Blower BlowerO BDate TruckWash
5602 06/21/21 781839 9704 789635 12/10/20 764416 224781 12/10/20 764416 224781 781839 06/21/21 113 113 07/05/21 07/29/21

The blank field is a field the mechanic uses when he works on the truck - he puts the date/miles in when he does the servicing. there is a small column next to each section that he can mark if it is the oil, transmission, overhead, air/cab/fuel filter replaced, or blower/blower oil, or truck washed. The "Current" field is updated weekly from the drivers logs from the past week. the "due" field is a simpe "current"-"miles" with a conditional format behind it - when it gets down to 500 (close to being time to be serviced) - it turns a light pink, when it gets down to 0 (due and/or past due), it turns to dark pink).

I've attached a copy of the excel spreadsheet if anyone wants to see how I've got it set up - I've used it for about 15 years - my mechanics love it.

What I want to do is convert this over to Access. I already have the forms set up. I key the maintentance part in as follows:

Grease table: Mileage Table: Vehicle Table:
GreaseOilID MileageID TruckID
TruckID TruckID Company
Date WDate Truck #
Mileage WMileage Unit Type
Type
Loads

They link on Truck ID
The final report will be sorted by Company then by Unit type then by truck #

We have 2 companies, and several unit types.
Anyone have any suggestions as to how I can lay this out? I've been working on it a few days.

 

[theDBguy]

Hi. I think you can definitely create a database to track the service data and still produce a report similar to the Excel spreadsheet. However, how hard or easy to do that will depend on the table structure you selected to implement. It may be possible (and perhaps better) to keep all service data in one table, rather than the multiple table setup you mentioned. The report will probably end up using multiple queries, if you want to display each service point horizontally, like the spreadsheet.

 

[plog]

I already have the forms set up.

That's the last thing to work on when creating an Access database. The first and most important is tables.

Database normalization - Wikipedia

en.wikipedia.org

Can you post the database, specifically the table(s), to this thread?

 

[GinaStar]

Ug, I was looking at my above posts and my spacing did not work. I do have individual table for the servicing
I have a "master" table with the vehicle info:

TruckID (Primary key)
TruckNo (our actual truck #)
Company
Unit Type (Tractor, Trailer, Utility,Pickup)
VinNO
Etc fields (such as date purchased and other info)

I have a Mileage table that the mileage will be keyed in weekly

MileageID (primary Key)
TruckID (links back to Master truck table)
WDate
WMileage

I have a Grease Table that the Servicing will be keyed in

GreaseOilId (primary Key)
TruckID (links back to Master truck table)
GDate
GMiles
Gtype
GLoads

Since posting my question, I ended up getting it to work. I set up individual queries filtering for each of the type servicing that i needed (Oil, transmission, overhead, air/cab/fuel, blower, wash) then set up a master query for the Master table with a left join to the sub queries left joined via truck ID. It is now showing the fields that I wanted properly in the report. I still have some tweaks to do in the report, such setting a filter for the most recent date on the mileage for the trucks based on the weekly mileage table. Also have to finish keying in the actual data in the database. Lol, I've only been working on this particular form since yesterday. I've only restarted this database this week after not working on it in about 4 years. I've been by myself maintaining a full office with 26 trucks doing all billing, payroll, safety, dispatch, etc and haven't had time. We finally hired someone competent to work in the office with me and I've managed to turn some of the work over to her so I can work on other things now.

I will probably work on it some more later to streamline it - but it works for now and that's the main thing I wanted. I will go back later when I have more time and normalize all my field names and everything (yes, I've been programming since access 2.0 and I do know about normalization, but when I'm in a rush, I sometimes just throw something together, lol!)

 

[theDBguy]

Hi. Glad to hear you're making good progress. Good luck with your project.

 

[GinaStar]

I'm planning on going back and fixing up everything later and working on the flow. Right now, I'm just throwing everything together based on an older database that I had that I used with a previous company (see attached). I've not used the old one in about 7-8 years. The company I used it with closed up in 2014 and I'm planning on updating the old database to work with the new company. Most of the functions will be similar though. At one point, I had it to where it was linked with our personnel database (which was also set up in access) and could see which ones were going through tires, brakes, whatever and as safety director could "coach" them about their driving habit. - lol this was in the age before Electronic logging, drivecam etc! Having to refresh my memory on programming - haven't done anything since I worked for the other company!

 

[GinaStar]

the naming conventions were from Leszynski/Reddick Guidelines for Access SmartAccess, suggest that all objects should have a descriptive tag, placed at the start of the object name - clipped from where I read it in an Article by Dev Ashish in 2010 (lol, still have the article in my OneNote). The earlier database was originally created in Access 2.0 (lol, that was a *long* time ago!) and I am a self-taught programmer. All I have was what I learned online and from books - If the books/online said to use the naming conventions, that's what I did. Like I said, this new database has just been thrown together over the course of about 4 days, I'm planning to go back and neaten it up and streamline it and get everything working a bit smoother when I have a bit more time. We are about to get into our slower season so I will have a bit more time at work.