Super Query needed for finding Qualified Employees.

[wvmbark]

I have a table that stores employee training for aircraft mechanics. tblTraining has the following fields: TrainingID, EmployeeID, TrainingTitle, TrainingType, FleetType, Date.

The value for FleetType can be 727, 747, 757, or 767.
The value for TrainingType can be ENG, AVI, FAM, CAT, or ETOPS.

I need to determine which employees are qualified on which fleet types using the following criteria...

To be qualified on 747 Employee must have completed ENG, AVI, and FAM.

To be qualified on 727 or 757, Employee must have completed ENG, AVI, FAM, and CAT where the Date for the CAT Training is > the last day of the month a year ago.

To be qualified on 767, Employee must have ENG, AVI, FAM, CAT, and ETOPS where the Dates for the CAT Training and the ETOPS training are both > the last day of the month a year ago.

I'm going insane trying to figure out how to query for qualified employees??


Any help would be greatly appreciated!!

Thank you,

Mike

 

[lupins46]

Create separate queries for each qualification. Each query should contain the same fields in the result, plus an extra column to indicate the qualification such as ...
Qual:747

Once you have created the individual queries go into the SQL view of a new query and enter:

Select * from qry727
UNION ALL
Select * from qry747
UNION ALL
Select * from qry757
UNION ALL
Select * from qry767

 

[Lilliabeth]

I realize it may be too late to do anything about it now, and I also realize it is possible I do not understand your data entirely, but from where I sit, your database is not normalized.

You should have a table for Employees, a table for Training, and a table for EmployeeTraining. (Why are Employee ID and Training Title in the same table?)

 

[wvmbark]

Lilliabeth - Actually, I have an Employee table (EmployeeID, EmployeeName, etc.), a Training table (TrainingID, TrainingTitle, Training Type, FleetType), and an EmployeeTraining table (TrainingID, EmployeeID, TrainingDate). I then have a query that returns joins those table and returns the following (the example only shows one employee, but the actual query has about 50 employees)...

EmpID Fleet Type MaxOfEndDate
001 727 AVI 1/1/1998
001 727 CAT 3/1/2001
001 727 ENG 12/1/2000
001 727 FAM 7/1/1997
001 747 AVI 1/1/2004
001 747 ENG 12/1/2002
001 747 FAM 8/1/1998
001 767 AVI 10/1/2004
001 767 ENG 4/1/1998
001 767 ETOPS 9/1/2001
001 767 FAM 2/1/1998
002 727 AVI 2/1/2001
002 727 CAT 12/1/2004
002 727 FAM 5/1/2000
002 747 AVI 2/1/2005
002 747 ENG 10/1/2004
002 747 FAM 4/1/1995
002 767 AVI 2/1/2000
002 767 CAT 12/1/2004
002 767 ENG 11/1/2003
002 767 ETOPS 12/1/2004
002 767 FAM 1/1/2000

Employee 001 would be not be qualified for 727 because Date for the CAT record is greater than 1 year old. He would be qualified for 747 because he has had FAM, AVI, and ENG (CAT not required for 747). And he would not be qualified for 767 because no CAT training.

Employee 002 would not be 727 qualified because no ENG training, but would be qualified for 747, and also 767 because he's had FAM, AVI, ENG, and his CAT and ETOPS are current (they will both expire 12/31/05 and he will no longer be qualified).

lupins46 - I'm still trying to figure out how to show all employees qualified for a particular fleet. I will then try your suggestion and post back.

Thanks to both of you for posts! I am a beginner so I welcome any advice or recommendations with this. Currently all the data is in Excel and I'm just building the database, so if a different approach is needed please let me know.

Thanks!!!

Mike

 

[wvmbark]

Pardon the typo... the example shows 2 employees -Mike

 

[PHV]

Just a though: you may consider the Batch feature of ADODB.

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

 

[PHV]

OOps, sorry, wron thread ...:~/

 

[lupins46]

'To be qualified on 747 Employee must have completed ENG, AVI, and FAM.'

Something like this...

SELECT EmpID, Sum(Abs([Type]='Eng')) AS Qeng, Sum(Abs([type]='avi')) AS qavi, Sum(Abs([type]='fam')) AS qfam
FROM [mytablenamehere]
where fleet = 747
GROUP BY EmpId
HAVING (((Sum(Abs([type]='eng')))>0) AND ((Sum(Abs([type]='avi')))>0) AND ((Sum(Abs([type]='fam')))>0));