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

 

[lespaul]

let me think about it and I'll get back to you.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases: The Fundamentals of Relational Database Design

 

[MajP]

With out doing this, I am not certain my logic is right, but I am doing something very similar in a database.

You need to create the following reference tables:
A table that list the events that can be used for a given fleet. This is a many to many relationship. A specific training can be used for many fleets, and a specific type uses many events. So this is that matrix you mentioned

joinTblTrainingEventUseableByFleet
trainingID
fleetID

Link this table to your tblTraining by Training ID.
Link this to your tblEmpTraining by TrainingID.
This new query should show all completed events, for fleet. You should see 2 records for (GENF6) one for 767 and one for MD11. I would save this Query
qryCompletedTrainingAndFleet

Now you need a second reference table. This table is very similar table, which is requred events by Fleet. I think that this is just general events, according to your buisness model

joinTblRequiredTrainingByFleet.
qualifyType (fam , eng etc)

Now link the joinTblRequiredTrainingByFleet to qryCompletedTrainingAndFleet by QualifyType. This should be an outer join so that you show all required training. If qryCompetedTrainingAndFleet does not have a required training Type you will get a null record on the right side of the join. Basically if you have no nulls you have a qualified person.

 

[MajP]

Correction the last, sent it too fast.
...


joinTblRequiredTrainingByFleet.
FleetID
qualifyType (fam , eng etc)

Now link the joinTblRequiredTrainingByFleet to qryCompletedTrainingAndFleet by QualifyType and FleetID. This should be an outer join so that you show all required training type by fleet.

 

[MajP]

A couple more things. Sort this last query by emp id, fleet ID, qryCompletedTrainingAndFleet.QualifyType. This should group together all the required types of events by person and fleet. Uncompleted types will come first.

You can then easily use this query to show reports of:
Aircraft type and qualified employees
Employees and aircraft they are qualified on.
Training events required by employee for a type aircraft qual.

 

[wvmbark]

MajP,

Thanks your assistance! I'm having a problem getting the last query to show the null records. It returns the same 531 records that the joined qryCompletedTrainingAndFleet does. I've linked both fields and the results are the same with either Left or Right joins.

The 531 records is accurate regarding the number of trainings completed that would count towards a Controller being fleet qualified.

Here's the 2 queries...

qryCompletedTrainingAndFleet
SELECT tblEmpTraining.[GEMS ID], joinTblTrainingEventUseableByFleet.Fleet, tblEmpTraining.TechTrainingID, tblEmpTraining.EndDate, tblEmployees.EmpType, tblTechTraining.QualType
FROM tblEmployees INNER JOIN (joinTblTrainingEventUseableByFleet INNER JOIN (tblTechTraining INNER JOIN tblEmpTraining ON tblTechTraining.TechTrainingID = tblEmpTraining.TechTrainingID) ON joinTblTrainingEventUseableByFleet.TechTrainingID = tblTechTraining.TechTrainingID) ON tblEmployees.[GEMS ID] = tblEmpTraining.[GEMS ID]
WHERE (((tblEmployees.EmpType)="Controller"));

qryQualifiedTrainingByFleet
SELECT qryCompletedTrainingAndFleet.[GEMS ID], qryCompletedTrainingAndFleet.Fleet, qryCompletedTrainingAndFleet.QualType
FROM joinTblRequiredTrainingByFleet LEFT JOIN qryCompletedTrainingAndFleet ON ([joinTblRequiredTrainingByFleet].QualType=qryCompletedTrainingAndFleet.QualType) AND ([joinTblRequiredTrainingByFleet].Fleet=qryCompletedTrainingAndFleet.Fleet);

Any ideas why this isn't working??

Mike

 

[MajP]

I am not the greatest at SQL, but this seems right. Maybe try this. Make another qry. Take the joinTblRequiredTrainingByFleet and tblEmployee and select employee ID as well. Without linking these tables this should creat a cartesian product. You will get every employee ID associated with every record in joinTblRequiredTrainingByFleet. (I used these ridiculous long names just to be descriptive). Call this query, qryReqEmpTrngFleet
Now use this query in qryQualifiedTrainingByFleet instead of
joinTblRequiredTrainingByFleet. Link this table by, qual type, fleet, and EmpID. Left outer join.
Lets say you have 100 employees, 5 aircrafts, and 3 required training events types per aircraft type. Then qryReqEmpTrngFleet should have 1500 records. Of those 531 should be linked and should see nulls for the rest.

 

[wvmbark]

OK, so now I have the 531 training events that count towards being a Controller being qualified AND 450 records where QualType is null. I still don't have a list of qualified controllers??

Is there any way to use joins to return only qualified controllers or will I have to utilize IIf statements to identify who has what QualType's on each fleet, etc.? I can work out how to do the latter, but was hoping for something more... simple.

Mike

 

[MajP]

Ok. You have all your answers in this query. With a little work.
First make a query that shows each person and the aircrafts that they are not qualified to work on. Something like

qryPersonNotQual

Select Distinct Emp ID, Fleet ID, etc from qryReqEmpTrngFleet where QualType is null

You have QualType on both sides of the join so this needs to refer to the field on the right side

Now to find the people who are qualified. Sorry about this, but build another query. Do another cartesian product between employee table and fleet table. Select each employee ID and Each Fleet ID. This table will list each employee and every aircraft. Call this qryEmpFleet.
So you have a query with all people and all aircraft, and another that has every person and the aircraft that they are not qualified in. So to find the opposite, you need to return all the records from qryEmpFleet not in qryPersonNotQual (aircraft they are qualified on). Do something like

qryQualByFleet
select EmpID,Fleet ID from qryEmpFleet where (empID Not In(select empID from qryPersonNotQual) and Fleet ID not in(select Fleet ID from qryPersonNotQual))

 

[MajP]

If you do not like the subqueries you can do an left outer join on qryEmpFleet and qryPersonNotqual linked on EmpID and Fleet ID. Then filter on qryPersonNotQual.EmpID is null.

 

[wvmbark]

I've just noticed that if I use tables as the left join, I don't get the null values from the right side. But if I first create a query from the table and use it on the left, then it works... Is it the primary key that's causing the difference in returned values???

 

[PHV]

Testing right side values (other than null) in the WHERE clause defeats the OUTER join ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[wvmbark]

PH,

I'd really like to understand what you're saying. Could you break that down for me? What do you mean by "testing" right side values? Was your statement in regards to my question about how the query results are different if I use a table as the left vs. a query of the same table as the left?

Thanks!

Mike

 

[MajP]

I think what he is saying if you have a left outer join, but filter on the values from the right side excluding the null (not sure about sorting or calculating) you basically get returned an inner join. I can not see where we did that though.
Anyways where are you in the process? I think as long as you got the two reference tables

joinTblTrainingEventUseableByFleet
joinTblRequiredTrainingByFleet

You can get what you need (and more). I could have gave you code to do this in one step, but if you get the queries working you will have much more utility on what you can do.

 

[lespaul]

check out Understanding SQL Joins

 

[wvmbark]

lespaul,

Dig the name... is it yours, or is taken from the musician or the guitar?

Thanks for the link (I actually ran across it earlier in one of your other posts and had just read it prior to my post asking why I don't get the null values on the right side when using the table as the left, but do when using a query of that table. I'm still hoping someone will help clear that up for me.

Mike

 

[wvmbark]

MajP,

"Where are you in the process?" Well, currently I'm trying to un-confuse myself and understand what I've got. I now understand the make-up and use of the cartesian products, the reference tables, and the outer joined queries for getting to my ultimate result (Which Controllers are qualified on which Fleets).

Right now I'm stuck (again) on your second step:

joinTblRequiredTrainingByFleet.
  FleetID 
  qualifyType   (fam , eng etc)

Now link the joinTblRequiredTrainingByFleet to qryCompletedTrainingAndFleet by QualifyType and FleetID. This should be an outer join so that you show all required training type by fleet.

This is where I ended up with the same 531 records as in the first query. I never did get the null records AND I'm not sure what I was supposed to do with this information since we later had to create the cartesian product qryEmpFleet to actually get the qualified controllers. Do I still need this? Do I need the nulls? Or do I go skip this and now join qryEmpFleet to the first first query qryCompletedTrainingAndFleet???

Mike

 

[lespaul]

it's my standard user name for most things - my name - leslie; my husband is paul, therefore together we are lespaul and between the two of us we might be able to play 'mary had a little lamb' on the guitar if we tried hard!

Glad the link was helpful!

Have a great day!

leslie

 

[MajP]

The reason is that determining who is not qualified for a specific aircraft is easy. You just take the required training type per aircraft and match it with a completed qualifying training type for an aircraft. If there is not a match the person is unqualified. This is much easier (unless you loop in code) then to determine that there is a match between every required type and every completed qualifying type for a given person.
This is a common trick you see in mathmatics. For example often it is either extremely difficult or mathmatically impossible to determine the probability of an event happening, but it is rather trivial to determine the probability of an event not happening. So you determine the probability of the event not happening and subtract that from the total probability (usually 1)
So once you determine who is not qualified for a specific aircraft, you subtract that out of the list of all people and all aircraft. The result is qualified people per aircraft.
How are you with writing code? I can write the code that will do this. It is not that difficult but requires 2 nested loops and 4 recordsets, and unless I get all your table and field names exact you will have to debug. However, the result is more intuitive. It is a table with Emp ID, Fleet ID, and a boolean for Qual or Not Qual.

 

[BobRodes]

I'm just looking at the beginning requirement. It seems you have the following kernel entities:

Employees
Classes
Training types
Fleets

With the following relationships:

Employees-classes M-M
Employees-Training types M-M
Employees-Fleets M-M
Classes-Training types M-1
Training Types-Fleets M-M

In other words, you have a heck of a lot of many to many relationships, and it's understandable that it would be a bit confusing. First, create an associative entity to resolve each of the many to many relationships:

EmployeeClass
EmployeeTrainingType
EmployeeFleet
TrainingTypeFleet

Now, you set up initially what you know initially, and then look at how flows of behavior are kept. So, initially, enter data for:

Each of the kernel entities
TrainingTypeFleet

Since you know already what all of these are. Then, evaluate the process and what data entry it implies:

1. Employee schedules a class. Entry in Employeeclasses.
2. Employee completes a class. Entry in Employetrainingtype, since if he completes a class, he's now qualified for the training type that class is for. Next, run a procedure that compares data in Employeetrainingtype and TrainingtypeFleet. If all the training types that are in a given fleet are also in the Employeetrainingtype table for given employee, enter a record for that employee in EmployeeFleet.

I believe this satisifies all of the requirements that you have laid out. You can tweak it a bit, say avoid performing the fleet calculation for employees that aren't going to get fleet qualifications, that sort of thing, but I believe this is the structure that you are looking for.

To answer your last 3 questions: 1) Yes, TrainingtypeFleet in the example above. 2) I believe I've described the calculation clearly, if not please post back. 3) N/A.

Finally, to deal with your ancillary requirements:
<-Not all trainings counts towards a fleet qualification.
Then they don't have an entry in ClassesTrainingTypes, since each training type has to do with a fleet qualification.
<-Other employee types (analysts, supervisors, etc.) complete training and is also tracked in tblEmpTraining.
They won't have an entry in EmployeesTrainingtypes, since again that would have to do with employees who were seeking fleet qualifications. Also, you could perform the fleet calculation only for employees that are controllers.

HTH

Bob