need to pull who hasn't had a training

[Guest]

Here's what I got

Table 1:
name=Employees
key=EmployeeID
fields=address, phone, active/inactive

Table 2:
name=Trainings
key=EmployeeID, Training
Fields=Date taken, Late(yes/no), Previous Due Date, Date Turned in/Taken if late

The last two fields are only used if the late box is marked yes, then the previous due date becomes the expiration date that was on the form

Table 3:
name=Training List
key=Training

That is the only field I have in that table since each class is unique. I have several courses listed under that field. Examples, CPR, 1st Aid, Med Level, PDT, PBS. So the way it stores it on the Training table is: Employee ID, Training, Date Taken.

What I want to be able to do is run a qeury that will pull up the names of employees who are missing trainings and have it list something. I want it to say list the name and the trainings that name has not had, i.e John Doe, CPR and 1st Aid. I cannot for the life of me get this to work how I want it. I can get one to work pulling up only one of the trainings, but how can I get it to list all the trainings that person hasn't taken??

 

[John Vinson]

Here's what I got

Table 1:
name=Employees
key=EmployeeID
fields=address, phone, active/inactive

Table 2:
name=Trainings
key=EmployeeID, Training
Fields=Date taken, Late(yes/no), Previous Due Date, Date Turned in/Taken if late

The last two fields are only used if the late box is marked yes, then the previous due date becomes the expiration date that was on the form

Table 3:
name=Training List
key=Training

What I want to be able to do is run a qeury that will pull up the names of employees who are missing trainings and have it list something. I want it to say list the name and the trainings that name has not had, i.e John Doe, CPR and 1st Aid. I cannot for the life of me get this to work how I want it. I can get one to work pulling up only one of the trainings, but how can I get it to list all the trainings that person hasn't taken??

A Totals query will work here - you may need to base a query on
another query though. Create a Query joining Employees to Trainings,
and Trainings to [Training List]. Select each join line and make it an
"outer join" - "show all records in Employees (or [Training List],
depending on which line it is) and matching records in Trainings".

Change this to a Totals query; broup by EmployeeID and Training, and
put a criterion on Trainings.EmployeeID of 0 to select those records
where there is data in both "one" side tables but none in the
Trainings table.

 

[John Vinson]

Thanks....I'll try that. What is the exacts of an outer join?? Is that like a many to one relationship?

To get an Outer Join, create a Query in the query design grid, and
click on the join line itself. You'll get three options: the default
inner join will show matching records in both tables; the other two
will "show all records in <first table> and matching records in
<second table>" and vice versa.

If you look at the SQL, the first option gives you an INNER JOIN; the
other two give you a LEFT OUTER JOIN and a RIGHT OUTER JOIN
respectively.