Displaying a lack of data.

[Lostsamoan]

Not sure the title is the best way to describe my problem. lol

Well i have only been working on Access for a few months. The database i am working on keeps track when employee have been trained in a certain SOP document. I have a table for employees names and info, a table for SOP names and info, and a look up table that includes both employee and SOP with the date that they have been trained.

Is it possible to create a report that would display what they did not train yet? Forgive me if its a stupid question and any help would be great. Thanks for your time.

Aloha

 

[jzwp22]

It sounds like you need to create a query using the Unmatched Query Wizard. You can then base your report on that query.

 

[neileg]

The way I would tackle this is as follows.

First you need to generate a dataset that contains every possible combination of employee and SOP. To do this, create a query based on your employee table and the SOP table. There should be no join between these tables so that you produce what is known as a Cartesian Product, i.e. every possible combination of employee and SOP. You normally try to avoid a Cartesian Product, but its what you want now.
Second, creat a query that joins the query you have just made with your lookup table. Use a left join (type 2 join) so that you get all the records from the new query and the values from your lookup table that match. Where the results from the lookup table are null, the employee hasn't yet done the SOP. You can generate this second query using the unmatched wizard if you want.

 

[Lostsamoan]

thank for the suggestions, I will try it out and let you know the results. Thanks again. I really do appreciate it.

ALOHA

 

[Lostsamoan]

Just wanted to thank you again. just tried it and thats exactly what i needed. Also learned something new so thats great also.

ALOHA

SIMI