Query for missing records

[jshay]

I am looking for help with a training database I am working on for work. I have a table with training requirements and another table with training records. What I have been trying to figure out is how to pull out the training deficiencies.

For example:
Joe is required to be trained on 1, 2, and 3 according to the training requirement table. There are training records in the table for 1 and 2 but not 3 in the training record table.

So how can I search the training record table to find the missing training requirement?

Thank you in advance!

 

[fredka]

You did not provide much to go on (field names, pk's, etc)

So, generally speaking....

you should use a query and link up the two tables - on your joins you want to pull all of the records in the table with the requirements....

in the query results, make sure you include the field that represents the number satisfied (in the table with training records) and use a criteria of null in that field. Meaning you will only be pulling the records where there is NOT a matching record in the training records table.

Fred

 

[WildHare]

Jshay, FredKa is correct - you want basically what is called an "outer join" - one that returns records from Table1 that DO NOT have a match in Table2, e.g. the 'missing' records.

Check for FAQs or WhitePapers here on Tek-Tips, keying on the "outer join" guy. I also have a quick note on my site all about joins:

http://www.jmhare.com/joins.htm

Hope this helps

Jim

--------------------------------------
"For a successful technology, reality must take precedence over public relations, for Nature cannot be fooled." - Richard P. Feynman

 

[lespaul]

The understanding SQL Joins link below is also helpful.



Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[PHV]

Furthermore, you may simply follow the "find unmatched records" query wizard.

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