Training db table relationship layout help needed 1

[wvmbark]

I'm creating a database to track aircraft maintenance employee training. One significant requirement for the db is to determine who is qualified to work particular fleet types.

The basic layout so far is...

tblEmployee
EmpID (PK)
LastName
FirstName
EmpType (Analyst, Controller, Superivsor, etc.)
...other

tblTraining
TrainingID(PK) (727FAM, 767CF6, GENCF6, MD11CF6, etc.)
QualifyType (FAM, AVI, ENG, ETOPS, CAT)

tblEmpTraining
EmpID(FK)
TrainingID(FK)
EndDate
...other

tlkpFleet
Fleet(PK) (727, 747, 757, 767, A300, MD11)

Controllers must be qualified to work a particular fleet (727, 747, etc.) I need to be able to determine which controllers are qualified to work which fleets. This gets real crazy so please hang with me...

To be qualified for a particular fleet, a controller must complete 3-5 specific training types. They must always complete 1 each of FAM, AVI, and ENG. And, on some fleets they must also complete CAT and/or ETOPS.

-Some trainings count towards more than one fleet.
-All trainings only count toward one training type.
-If 2 or more trainings are for 1 type, controller only needs 1 (I.e, GENCF6 counts as ENG for either 767 or MD11)
-Not all trainings counts towards a fleet qualification.
-Other employee types (analysts, supervisors, etc.) complete training and is also tracked in tblEmpTraining.

Examples of trainings and types:
727FAM FAM
727AVI AVI
727ENG ENG
727CAT CAT
Note: controller needs all 4 to be 727 qualified)
767FAM FAM
767AVI AVI
767CF6 ENG
GENCF6 ENG (GENCF6 counts as ENG for 767 & MD11)
767CAT CAT
767ETOPS ETOPS
Note: needs 1 ea FAM, AVI, ENG, CAT, ETOPS for 767 qual)

MD11 has 4 trainings that can count as ENG, 1 of which counts also for A300, and another for 767 (see GENCF6 above).

Do I need to create another table that is basically a matrix of qualified trainings for each fleet? If yes, how do I utilize this table in determining qualified controllers? If no, what is the best approach to making that determination?

Thanks for any assistance!!!

Mike

 

[MajP]

I was not clear in my post "9 Jan 06 15:12 ". You have to build the qryReqEmpTrngFleet. The original method of using (joinTblRequiredTrainingByFleet) was just wrong.

Again this query is the required training for each aircraft table (joinTblRequiredTrainingByFleet) with a cartesian product of all controllers. So this query has

qryReqEmpTrngFleet
EmpID FleetID QualifyType (required)

This table should be about this big (6 aircraft types) X (average 4 qualifying types) X (amount of employees)

Now link the qryReqEmpTrngFleet ( not joinTblRequiredTrainingByFleet) to qryCompletedTrainingAndFleet by QualifyType, FleetID and emp id. This should be an outer join so that you show all required training for each person by qual type and by fleet linked to the completed training. Everything else remains the same. If this works you are golden.

 

[BobRodes]

<It is a table with Emp ID, Fleet ID, and a boolean for Qual or Not Qual.

Or just a table with emp id and fleet id only, that doesn't have an entry for people that aren't qualified.

B

 

[wvmbark]

MajP - It's working now... it just took me some time to back track through your posts and figure out what I needed, what I didn't, and what order to put it all in. Thanks much for all your help!!

Now I have to figure out the best way to create the input forms for the reference tables, etc. since it will be an administrative employee using the database. It's gonna need to be as least confusing for the user as possible which may prove to be a difficult task... For example, if a new training is added, then have to add the fleet types it is associated with and the Qualify Type if applicable. If a new fleet is added, need to either update current trainings if applicable to that fleet, otherwise enter new ones with associated Fleets and Qualify Types. Hopefully, I can figure a way to do this without having to use 15 forms...

BobRodes - Thank you also for you input! I don't have a clue about code, and the fact that I'm more of a user than a developer, I'm gonna stick with what MajP has laid out since it is both working well and is understandable for me.

Mike

 

[MajP]

To add new training required training type.
Have a main form with the aircraft type. The subform is based on based on the joinTblRequiredTrainingByFlee linked by FleetID. You will have a combobox that add a new qualify type.

To add a new qualifying event. Your main form is based on the training table. The subform is based on joinTblTrainingEventUseableByFleet linked by training event. You will have pull down to add a fleetID.

 

[wvmbark]

Sounds easy enough... I'll post back if I run into any problems. Right now I'm trying to figure out another problem which I posted earlier today on the Queries forum... If it's not one thing it's another, egh?

Thanks again, you've been a great help!!!

 

[MajP]

I will take a look.

 

[BobRodes]

Well, if it ain't broke, don't fix it! If it ever GETS broke, take a look at that post. MajP's very helpful posts are about getting the data you're looking for from your existing schema. My post is just how I would organize your data structure. Your schema is probably similar now to what I gave you, but there may be some differences that will be worth looking into at some future date.

Bob