Any Idea how I might... (1 Viewer)

[accessfleet]

I am using access2007 to document vehicle maintennance repairs.

I have been thinking about the next revision.

In the Workorder “instructions & crew remarks” I have add a default value “check inspection expiration date, lights, tire pressures and fluid levels”

I would like to expand this a little.

From the Work Type drop down, when “PMA Maint” is selected I would like to append the “instructions and crew remarks” with additional text


1. Change oil and filter including mounted equipment.
2.Lubricate chassis, door locks/hinges and grease all fittings. Include all mounted equipment also.
3.Visually check brake lines/hoses, belts, steering linkage, shock absorbers, rear springs, exhaust system,
and universal joints.
4. Adjust brakes as needed.
5.Check all fluid levels on brake master cylinder, steering, differential, transmission and battery.
6.Check starter draw and alternator output and battery connections clean battery cable ends and post.
7.Check all lights and repair if necessary. Including trailer connection.
8.Check tire pressures, record tread depths. Inspect tire condition and rotate if needed.
9. Drain air tank if equipped with air brakes.
10. Check all hydraulic hoses, motors, gauges, controls & fluid levels. (Mounted Equipment)
11. Do an operational check & lube all bucket pins, hinges.

Or, From the Work Type drop down, when “PMC Maint” is selected I would like to append the “instructions and crew remarks” with additional text

1. Change oil and filter including mounted equipment.
2.Lubricate chassis, door locks/hinges and grease all fittings. Include all mounted equipment also.
3.Visually check brake lines/hoses, belts, steering linkage, shock absorbers, rear springs, exhaust system,
and universal joints.
4. Adjust brakes as needed.
5.Check all fluid levels on brake master cylinder, steering, differential, transmission and battery.
6.Check starter draw and alternator output and battery connections clean battery cable ends and post.
7.Check all lights and repair if necessary. Including trailer connection.
8.Check tire pressures, record tread depths. Inspect tire condition and rotate if needed.
9. Drain air tank if equipped with air brakes.
10. Check all hydraulic hoses, motors, gauges, controls & fluid levels. (Mounted Equipment)
11. Do an operational check & lube all bucket pins, hinges.
12. Replace air filter. Including brake air compressor unit.
13.Replace fuel filter and PCV valve. Drain fuel water separator if so equipped.
14.Engine tune-up. Replace spark plugs. Check ignition system.
15.Repack bearings, replace wheel seals and adjust bearing preload.
16. Pull wheels and inspect brakes replace if necessary.
17. Service automatic transmission, replace trans filter.
18.Change gear lube on manual transmission and differentials.

 

[Galaxiom]

Neither the way you are doing it now nor the proposed technique use the features available in a relational database. Your data structure is not fully normalized.

Storing all that text and in particular appending it is inefficient and clumsy. The descriptions of standard actions should be in a separate table against an Integer field such as ActionID.

The standard actions taken in a job should be stored as multiple records in a related table as the ActionID and related to the Maintainence table via JobID. Different workers can record separate actions on the same job with their usercode and time stamps.

A Comments field (text or memo) provides the detail for the Action. General comments could be recorded against a special ActionID (eg 0) record.

Non standard actions can be stored by allocating a special ActionID.
You could consider a range of numbers (eg Negative numbers) to designate particular genres of special actions.

Personally I would stick to text for Comment fields and work with the 255 character limit. Memos have other limitations and are notoriously unstable. Long comments can always be handled by adding another Action record. Indeed the Action record can have a field to indicate that another record is related directly to it so they are sequentially displayed even if another record has be inserted by another user between the two.

 

[accessfleet]

Thanks for thje reply. The system has been fully functioning since 03/2010 and all ready contains 1000+ workorders it seems pretty normalized to me.

The detail I am refering to would only be printed on the closed work order to inform the user of the service work that was done. Of course it could also serve as a management tool when maintenance items are carelessly overlooked on a reoccuring basis by mechanic doing the work.

 

[missinglinq]

...The system has been fully functioning since 03/2010 and all ready contains 1000+ workorders it seems pretty normalized to me.

That's because you haven't taken the time to learn about normalization! Take heed of GalaxiomAtHome's advice or you'll pay the price in the future!

Linq ;0)>

 

[vbaInet]

That's because you haven't taken the time to learn about normalization! Take heed of GalaxiomAtHome's advice or you'll pay the price in the future!

Linq ;0)>

...

http://support.microsoft.com/kb/283878
http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx
http://www.databasedev.co.uk/database_normalization_basics.html

 

[gemma-the-husky]

I would have thought - the standard tasks really need to be in another table

then you can set up a service for a vehicle

so vehicle KZ54 DFY
has a full service on 17/1/2011, with checks 1,3,6,7,9

has a minor service on 31/3/2011, with checks 2,3,4,5

------------
to do this you need tables (MAYBE) for

vehicles
services
service tasks

standard service types
standard service task

it depends somewhat how flexible you need to be with the tasks assigned to each service.

------------
please understand also, that a lot of our responses are based on a "instant" reaction to the problem. However, a full analysis often yields other complexities that need to be taken into account.

 

[accessfleet]

Thanks for the guidance Gemma.

I am still a student of Access 2007, My resources are

MS Access Power Mastery 2007 course
MS Access 2007 Livelessions
Access 2007 The Missing Manual
Access World Fourms learned brethern

For the benefit of GalaxiomAtHome and Missingling I will review normalization again and maybe I'll see something making the "whole" more understandable. I am all for more and better learning. Why else would a Fleet guy endeavor to learn Access for the purpose of developing better fleet management software than is commercially available?

Thanks to all, who have taken the time to teach an old dog a few new tricks.

 

[Galaxiom]

Thanks for thje reply. The system has been fully functioning since 03/2010 and all ready contains 1000+ workorders it seems pretty normalized to me.

There are degrees of normalization and there is certainly room for more in your database. Basically any text that is repeated many times should be normalized by storing it as an ID which refers to the record in another table that holds the actual text.

The detail I am refering to would only be printed on the closed work order to inform the user of the service work that was done. Of course it could also serve as a management tool when maintenance items are carelessly overlooked on a reoccuring basis by mechanic doing the work.

Even more important to normalize this information. In the normalized Tasks you will search for a TaskID in the job. In your existing system you will be searching for a substantial text string in a large text field. The time difference to perform those tasks will be measured in orders of magnitude.

It is small in actual time if you are looking for onerecord. However if you wanted to find all the jobs where a particualr task was performed, the normalized version will return the records in a flash. Once you get a large number of records, the denormalized version should include a messagebox to suggest you go and make a coffee while it churns through every word in that field for every record.

You can still search the text of the comment for a particular word in the normalized version. The difference is that the Where clause in the query returns all the TaskIDs that include the string, then the join to the main table returns all the records with those IDs.

When normalizing text like this you must be aware of the effect of changing the text in the linked table. It will change every record including those from the past which may be unacceptable in many circumstances.

There are two ways to deal with this. Don't allow the text to ever be changed. This is a bit clumsy as the new record will have an entirely different ID so your searches might have to be modified to search every version ID to get what are essentially the same Tasks.

The other way is a related table. This table holds the history of all changes in the Task description. It has, at least, fields for TaskID, ChangeDate and TaskDescription. Each time you change the description it stores a new record.

In the RecordSource of the forms and reports, this table is linked via the TaskID and the TaskDate. Effectively, the query returns the appropriate text for the record with the TaskID and the Max(ChangeDate) that is less than the TaskDate in the Task record.

To potentially avoid saving descriptions that have never been used, the system that stores the updated description can check if the previous version of the record had ever been used. If it is unused then update the existing record instead of creating a new one. This saves having a lot of junk records when the user is refining the system before they really get going.

Your project sounds really worthwhile and you seem committed to excellence. The data structure is the key to making it better than available commercial packages. It is vital to get the foundations right and normalizing this stuff is critical to the database performance. Try to imagine how it will perform with millions of records and you will see the importance of this level of normalization.

If you ever wanting features like multiple mechanics on each job this level of normalization is essential. Get the structure right now even if it takes you weeks because changing it later will require a huge effort to rebuild the forms and reports.

One day you might find you are running a Fleet Database company instead of a fleet.

 

[accessfleet]

Hi Galaxiom:

Thanks for your post you have given me a lot to think about as I look over my DB in the next week or so.

Your guidance ,if I understand it correctly is to
1) create a new table.
2) relocate “check inspection expiration date, lights, tire pressures and fluid levels” from the default value of the instructions field of the workorder table to a field in the new table
3) add each maintenace item to the new table

4) Use worktype to flag which maintenance items are printed on the management review copy of the work (file copy)

I appreciate your Interest and help.

John.

 

[Galaxiom]

I think you have the basic idea of it. If I understand correctly you are currently storing all the tasks in a single field in the Job record.

In the normalized version there are at least two extra tables. One is the Lookup table for the MaintenanceItemID and its description allowing you to refer to the MaintenanceItem by its ID.

The other table stores MaintenanceItemIDs as separate records against the JobID (the FK off the Jobs table).

Essentially the relationship between Jobs and MaintenanceItems is many-to-many via the Tasks table which is a Join Table in database terminology.

The Tasks can easily be displayed in a subform in Datasheet or Continuous Forms mode.

Another thing you might want to consider for selecting tasks is cascaded combos. Rather than one massive list of tasks, group them by System. Choose the System in the first combo which sets the next combo RowSource to a small number of choices in the system selected in the first combo.

In this setup the Task table records carry a SystemID field that links them to the first combo RowSource.