Solved Multiple inspection criteria for a form

[PaquettePaul]

I am sure that this has been answered before. However, I did not see it in recent threads.

I have two instances where a user need to see the latest information on multiple criteria or records associated with a single event that evolves over time. For example,
- an aircraft has several (20-40) inspection criteria that must be addressed over time or hours of service.
- when the plane comes out of service, the user needs to look at the most current information for each criteria (which I know how to find), display that information in one display list (Okay), and then allow the user to specify which records need to be adjusted and create new adjustment/inspection records (not sure how).

while I could have the user enter details of each service item one by one, I think that selecting and updating from the list would be preferable. This same scenario applies in another case in my app. Not asking anyone to build my app, just not sure what a good design and approach would be and howto implement the design.

the database design Would be:
- an aircraft table
- an inspection plan table for the aircraft which how specific records, one for each criteria
- a service table which links a service event and each inspection criteria that was covered or fixed for the aircraft, the latest record for any criteria sets the timing for the next occurrence

thank you

 

[theDBguy]

It may not be in recent threads either, but you might try searching for "vehicle maintenance" samples/discussions as well. Just a thought...

 

[Pat Hartman]

I think you might be missing a couple of tables.
You need three tables to define ServicePoints and inspections.
A table that lists all possible inspection types
A table that lists service points. I.e. 100 hours, annual service, break heck, etc.
For a service point, you have a child table that assigns all the inspection types for that service level. It is broken out this way because many inspections will be done at multiple ServicePoints and you don't want to risk InspectionA being defined differently for ServicePointA than for ServicePointB.

Then you need two tables to assign these to a specific plane to gather results
AirplaneServicePoints - which identifies when this service was performed
AirplaneInspections - which inspections were done for this ServicePoint and the results

 

[arnelgp]

Everything has their own pm system, especially for aircrafts.
you just need to find them out.
don't do it your own way, ask someone who knows the system well.
and build your db on founded standards.

 

[PaquettePaul]

This is a one person operation, and he keeps all his records manually and uses the automated tools for reminders and for student records.

I believe that the table following table list should be sufficient to purpose:
1) a basic code table to define the inspection/maintenance criteria names
2) aircraft table
3) inspection plan table that identifies hours, years, and other timing needed for each inspection/maintenance criteria (included the three timing criteria in a single record as years would reset both years and hours and it is simpler)
4) a flight segment log table that has all aircraft related flight activity as well as ground inspections and service related inspection/maintenance headers/instances
5) an inspection/maintenance activity table that is associated with the aircraft (and plan by inference) and the flight segment

The suggested tables for aircraft service points and aircraft maintenance points is covered by item 5.

My conundrum was how to make an easy data entry process. After puzzling it through, I figured I would do a join on the last activity (highest/maximum inspection primary key) for each inspection.maintenance criteria on the plan. That gives me a starting point for the current activity. Then I can use the results to calculate the hours and/or time that has passed since that point.

I can display the results in a form with unbound field/button for each criteria entry. Some data entry options are:
- User goes through the list and provides an indication as to which items had activity, select a button in the form header to apply all activity updates and reset check points if needed. Approach may be visually pleasing although difficult as I am not sure how to check each displayed record in this results list (although I could create a temporary table and interrogate the data on each record and then delete the temp table later)
- User could select a button which is provided for each item shown in the list. That way I could just do the update activity on a single record with a lot less fuss.
- I could skip the record list and provide a single data form that the user selects the inspection/main5enance item, the module goes out and collects the latest activity data, and then the user enters an update. would select a button to go through the list, and the module would create new activity records and reset any control points that may be needed.

I might end up going with a variant of the third option and leave the list of all criteria for a report.

With regard to building on established standards, vertical market applications rarely show the details of their applications and databases given that such information is considered proprietary knowledge. So, my view is based on the reporting requirements of Transport Canada, and best practices that my brother has used/seen over 30 years of flying. Not that I do not appreciate that recreating something that many people have previously created seems daft. If he buys an application, it would be beyond his budget at this time and overkill for his needs,

thanks for the feedback. If nothing else, feedback triggers further though and forces one to question whether the chosen path is appropriate.