Profile query

[Guest]

Hi

I have a table with employee names and training courses they have been on.
I have created a second table which has a training profile for each job
type, so for example Social Workers need to attend the Diversity course,
Manual Handling, the Induction course so on.
I want to create a query that pulls out a list for each person who is
missing the mandatory training set in profile.
So i end up with a list of names and courses that they need to go on.

I've tried to do a simple query, using an expression to check each
MandatoryCourse against CourseEmployeeBeenOn, but that produces the employee
name for as many courses that they have been on and leaves a blank where they
need to go on a course...so not very helpful.


Any help would be greatly appreciated.

 

[Steve]

You need to redesign your tables:
TblEmployeeType
EmployeeTypeID
EmployeeType

TblEmployee
EmployeeID
Firstname
MI
LastName
EmployeeTypeID
etc

TblTrainingCourse
TrainingCourseID
TrainingCourseName

TblTrainingCourseRequired
TrainingCourseRequiredID
TrainingCourseID
EmployeeTypeID

TblTrainingCourseTaken
TrainingCourseTakenID
EmployeeID
TrainingCourseID

You can now use an unmatched query to find the courses required for each
employee by his employee type in TblTrainingCourseRequired that are not in
TblTrainingCourseTaken for each employee. To create an unmatched query, go
to Queries at the database window, click on New and select the Unmatched
Query Wizard.

PC Datasheet
Providing Customers A Resource For Help With Access, Excel And Word
Applications
(e-mail address removed)