Help Needed with table structure for vehicle inspections

[rm.harper]

Hi!

(SKIP - this part if your not into 'the story')
It has been a while since I last posted. Thanks to the help of the forum members I've been successful in implementing a supplier invoice management database AND an operation management system. Both have been in play for well over a year now and they seem to be working as expected. Ive now had a shift in my job role at work mainly due to my work with the two databases and their success.

NOW! On to my next project!

My next work project is well underway and involves transport management. I have most of the basics now working within my database but before I begin playing with things I don't fully understand I though I'd get some advise if its kindly given.


Part of this database's role is to store essential maintenance and service records of vehicle inspections. (this is the tricky bit) - I have never had to include so much data into one part before and find myself lost.

In simple terms, I need to create tables to record data collected from the below form. However as you'll be able to tell it seems to fall into 3 sections, A tick list of inspection points followed by, if a defect is found, a brief description of the defect and actions taken to rectify said defect.

I have a slight idea how to begin to break this down into sizeable chunks. As not all inspection points will need the 'description of defect' AND 'action' parts filling out.

In my previous invoice management database I used the field InvoiceNO to join any existing credit note with its invoice.

My theory for this so far:

tblSafetyInspectionFormDetails
SIFID, SIFDate, SIFMileage, SIFRegistration

tblSafetyInspectionPoints
SIPID, SIPDescription

tblSafetyInspectionDefects
SIDID, SIDInspectionPointsREF (to use PK for Inspection Points but show description for ease of use), SIDDefectDescription,

tblSafetyInspectionRectification
SIRID, SIRInspectionDefectREF, SIRActionsTaken

Then using this I can create a form to easily fill all 3 tables in at once and print a full computerised report for digital filing. I will store hard copies however the reason for this is so I can then use the data to quickly create queries and reports on what items defect the most on a given vehicle etc...

Hopefully you can understand my rambling as I try to explain whats whizzing through my head.

Any help would, as always, be appreciated. I just want to start on the right foot to avoid nasty suprises once I begin using the database.

Thanks in advance.
Ryan

 

[The_Doc_Man]

Ive now had a shift in my job role at work mainly due to my work with the two databases and their success.

This is the way of the business world. It is a GOOD thing to have success in a project. However, let's hope you like doing this - because once you show you CAN, trust me... your bosses will assure that you WILL do this for them again and again. Or as we used to say with the government contracting arena, once you stink of Access, you will wallow around with Access for a LONG time. But, assuming this is something you like to do, congrats!

Now as to the question. My advice to anyone trying to design something is to make it more visual. This can often be accomplished with a dry-erase board and some sticky notes. Use the notes to represent tables, draw lines between them to represent relationships. From there, look at your desired OUTPUTS and work backwards to determine what inputs and/or processing you would need to get to the desired goal.

Which means you need to determine what reports and forms you will need for the proper operation of this beastie that you are building. In other words, work from end requirements to beginning data collection and management backwards. This is in line with my two favorite "Old Programmer's Rules."

#1 - Access won't tell you anything you didn't tell it first - or at least tell it HOW to compute what you wanted. Which is why I said "work backwards."

#2 - If you can't do it on paper (or a dry-erase board) then you can't do it in Access. Because you not only need to map out data, you need to map out transactions, transformations, relationships, etc. Until you are ready to define the way what you have becomes what you want, you don't know enough to touch Access. (Exception: Once you get the dry-erase/sticky diagram done, you can try to build some test tables in Access and use the Relationships panel to duplicate the drawing you made.)

Your tables have to make sense to you - we don't know your exact requirements and don't know if you have corporate standards for data layout, presentation, etc. You need to be thinking along the lines of a many-to-many (junction or linking) table already in order to link multiple possible conditions to multiple possible vehicles. However, you are correct to keep descriptive text in side tables that you can call up in forms via a simple JOIN query, but keep that information separate when you don't need it - e.g. when computing statistics, who needs descriptions?

Just a quick note in case you are not up on it: In a many-to-many table, it is easy to do statistical things, but remember that having a one-to-many relationship WILL INCLUDE the one-to-none case sometimes, and OUTER JOIN cases WILL NEED to protect themselves against having unmatched records leading to NULL fields in some queries. So look carefully into liberal use of NZ() functions in queries used for reports.

For example, if you did a summary fleet status report and it turned out that at least one vehicle didn't need any work this period, then to assure that ALL vehicles are included, you will have to use an OUTER JOIN that includes all vehicles and any details from the maintenance or inspection table. An INNER JOIN would omit vehicles that had no inspection or maintenance records. Therefore, when you lay things out at the high-level diagramming phase, don't forget to consider the "nothing happened this week" cases.