Query not finding all the correct unmatched records

[mamaw]

I have several tables that I am trying to get information from:

Clock Number Table with fields: Clock #, Name, Title, Dept, Term, HireDate, & TransDate.

Completed Training with fields: Clock #, Data Completed, Doc # & Rev Level

Linked Table, Controlled Documents with fields: Doc #, Title, Effective Date & Rev Level

Linked Table, Distribution Table with fields: Doc #, Distribution, Rev Level & Effective Date.

Some of the tables have more fields that those listed, but they do not pertain to this query.

I am trying to create queries that will provide me with the names of employees who have not been trained on Controlled Documents that have been distributed to their department.

So far, I have been able to determine if no one has been trained, but if even one person has been trained, they do not appear on my list. I would like to know the Clock # and name of those that have not been trained.

I have created 3 queries to get this far. Query 1 is the Clock # Table and the Completed Training Table joining the Clock #. Query 2 is the Controlled Document Table and the Distribution Table joining the Doc #, Rev Level. Query 3 takes these queries and joins Doc # and Rev Level. and pulls records where the Rev. Level and Doc # is Null.

 

[neileg]

Should this line:
Completed Training with fields: Clock #, Data Completed, Doc # & Rev Level
actually say
Completed Training with fields: Clock #, Date Completed, Doc # & Rev Level

Presumably, if the employee has not completed training then there will be no record in Completed Training. So your first query should return those employees where this is null. Then join this query with an inner join to your query joining Controlled Documents and Distribution Table. Any records returned from this query will identify the untrained receivers.

 

[mamaw]

Neil,

Thanks for your reply. You are correct in assuming that Data should be Date. (Typo)

I tried your suggestion, but it did not produce the records I am looking to receive. When I run the first query (Clock #), I get only employees who have no training records for any document if I place a "if Null" in the criteria for Date Completed. If I do not add this to the criteria, I still get the same records plus everyone who has ever been trained.

Perhaps, I should further explain that any given employee can be trained on many documents and many Rev Levels. Therefore, I have also joined Rev Levels. In addition, several or all departments may train on the same document. Then some documents are only trained by specific departments. I hope that clears up some of the issues that I am running into.

If you have any further suggestions, I will be happy to try them.
Thanks again.