Suggestions for a report? (1 Viewer)

GinaStar

New member
Local time
Today, 06:54
Joined
Nov 18, 2021
Messages
5
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.
 

Attachments

  • Service.zip
    36.7 KB · Views: 271

theDBguy

I’m here to help
Staff member
Local time
Today, 04:54
Joined
Oct 29, 2018
Messages
21,358
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

Banishment Pending
Local time
Today, 06:54
Joined
May 11, 2011
Messages
11,613
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.


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

GinaStar

New member
Local time
Today, 06:54
Joined
Nov 18, 2021
Messages
5
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!)
 

Attachments

  • Maintenance.accdb
    1.6 MB · Views: 329

theDBguy

I’m here to help
Staff member
Local time
Today, 04:54
Joined
Oct 29, 2018
Messages
21,358
Hi. Glad to hear you're making good progress. Good luck with your project.
 

Uncle Gizmo

Nifty Access Guy
Staff member
Local time
Today, 11:54
Joined
Jul 9, 2003
Messages
16,245
Assuming that you use a single spreadsheet to manage a service, then it follows that when you move your system onto MS Access you are going to need a service table "tblService" which would record the time and date of the service, and possibly some/all of the following:- the mechanic or mechanics performing the service, location of the service, etc...

This would be the top level a master form (main form) which houses all of the other forms, possibly subforms I reckon, not too sure. Depending how you need to put it together.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 19, 2002
Messages
42,981
You've actually done a good job. The transition from spreadsheet to database can be a difficult one because you get to see a smaller view of the data at one time when you are doing data entry.

Some things to fix.
1. call the close code from the unload event or remove the x from the form so the only way to close the form is with the close button.
2. It is poor practice to use embedded spaces or special characters in object names. Changing them now will be a PITA but I would still change the names that have # or other special characters in the name.
3. The Access default for a Long integer is 0. This is a problem and you should always change it to remove the default. Leave it blank and make the FK field required. I tried to enforce RI on the relationships but couldn't because some of the tables have bad data. Get rid of the bad data, fix the defaults and enforce RI.

Some things to think about:
1. You need to have multiple views of this type of data. For example, you want to be able to see everything that happened to a truck. You might need to use a tab control so you can stack the subforms so all the related data can be seen from one form. Then you want to have a view that shows the tire work, the grease work, etch. these will be your primary data entry forms. So I would make the subforms on the truck form not updateable. You really don't want to have to manage update code in multiple places. If you decide you want to update the tire data from the truck view or the direct view, then you should work on making procedures that you call from each form. It is a little more work up front but not much and you'll thank me later if you need to change something and you have only one place to change rather than multiple.
2. I prefer a more consistent view. You seem to have gone with forms from one of the Access versions prior to A2007. That's fine but I would use the theme feature to make them have the same colors.
3. Forms/Reports should always have specific sort sequences. If you are producing something related to trucks, you might start with a truck type and then the actual truck number so all the vans are together and all the dump trucks, etc.
4. Forms should have an easy way to find something so maybe add combo boxes in the header to filter the data rather than making the user scroll through every record.
5. Same thing for reports. They should either be for a specific vehicle or a time period.
6. When looking at a time period view, determine if it makes more sense to the user to sort the dates ascending or descending.
 

GinaStar

New member
Local time
Today, 06:54
Joined
Nov 18, 2021
Messages
5
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!
 

Attachments

  • Repairs Forms.mdb
    4 MB · Views: 334

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 19, 2002
Messages
42,981
I'm planning on going back and fixing up everything later and working on the flow.
When you are developing, neatness counts. It's like cleaning up after yourself as you are cooking. It is much less stressful to test and correct as you go.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 19, 2002
Messages
42,981
The changes you made to the column names are not productive. Using a prefix indicating the data type doesn't help anyone, let alone you and it interferes with intellisense as well as making ds view when you open a table useless since the actual name is obscured by the prefix. You appear to have gotten around that by using "pretty" captions. The problem with that is you will need to be using the caption names to reference the columns so we're back to bad names in code.

Get rid of the captions. Users NEVER see tables or queries. Only YOU see them. Users see forms and reports. Those have captions that can be "pretty" and includes whatever embedded spaces and special characters you want.
Get rid of the field prefixes.

PS autonumbers are NOT GUIDs so even the prefixes are inaccurate.
 

GinaStar

New member
Local time
Today, 06:54
Joined
Nov 18, 2021
Messages
5
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:54
Joined
Feb 19, 2002
Messages
42,981
Most people prefix objects but not Column names. If you want to prefix your variables that's fine. I'm pretty sure that article doesn't recommend prefixing column names.
 
Last edited:

Users who are viewing this thread

Top Bottom