unmatched query problem

[Skip Bisconer]

I have a table listing 147 Company training Policy# with each Position to which each policy applies. Not all Policy# applies to every position, i.e. Driver versus Office staff. There are 32 employees in 7 different positions.

I have a table of Employee Records with standard information including Employee ID and Position.

I created a select query from the Employee Record and the above training requirements that provides a data list of every employee to each pertinent training policy by inner joining Positions with three columns Policy# and Title from the Policy# table and Employee ID from the Employee Record table.

I am trying to create a query that will tell me by Policy# what Employee has no record of being initially trained in that particular Policy #. I have built a query but it doesn’t give me a complete listing of missing employees.

SELECT qryPolicyByEmployeeID.CWSPolicy, qryPolicyByEmployeeID.EmpID, qryPolicyByEmployeeID.Title
FROM qryPolicyByEmployeeID LEFT JOIN TrainingLog ON qryPolicyByEmployeeID.EmpID = TrainingLog.EmpID
WHERE (((TrainingLog.EmpID) Is Null))
ORDER BY qryPolicyByEmployeeID.CWSPolicy, qryPolicyByEmployeeID.EmpID;

My problem occurs at the “Where” statement. When inquiring on a specific Policy# and the EmpID is in the training log for any other Policy# that EmpID does not appear in the listing. I am trying to figure a way to get around this.

Has anyone a suggestion as to how I can make that happen? I am very flexible about changing table layouts if that will get me what I need.

 

[FoFa]

You basically need an AND condition in your left join to also state the proper policy #

It would be something like EmployeeID = LodEmpID and Policy# = LogPolicy#

 

[Skip Bisconer]

Thanks for responding,

I am not understanding what you are telling me. I'm just too new to this. How would the statement look in my query grid? Or how would you change the From statement?

 

[neileg]

In the query grid, drag a join between Policy# and LogPolicy#

 

[Skip Bisconer]

When I do that I get zero records. After reviewing the result of my original query I find I am only getting records from 4 employees who have had no training what so ever. There must be something small thing I am missing. The query seems to me a very basic one. Any more suggestions?

 

[FoFa]

On your two joins, make sure both are left joins (or right) the same way.

 

[Skip Bisconer]

I have found a problem with my tables that I am drawing this query from. I want to see what I can do to correct that and then see if I am still having this problem. Thanks for all of your suggestions.