Report of Who's Not In The Other Report 2

[JJOHNS]

In my job, we are required to take a certain number of classes each year to keep our license. I have a report that shows how many classes each employee has taken so far. Works great. The problem is those who have taken no classes yet are not showing up on the report at all. I need to either make a report of people with no classes for this year, or add them to the existing report with a zero for total units.

I have a Classes table and an Employees table (joined by EmployeeNumber). My first report selects records from the Classes table that have a renewal date of 2004. I need one that lists the employees with no classes with that renewal date.

I thought about using WHERE [renewal date] <> &quot;2004&quot;, but most of the employees have at least one class from 2003 or before, so that's no help.

Does anyone have a good idea for me?

 

[Aubs010]

If an employee has not had *any* classes, then they would not have any items in the classes table.

Maybe use a create query to create a temporary table then remove all duplicate employee numbers (so you can see all the employees that have had at least one course

Then, compare... the two Employee Number columns one from tblEmployee and the other from tblCourses

Sorry, but I'm not feeling well today and can't concentrate properly, so haven't got the patients to go through it properly!

Does that give you any ideas?

Aubs

 

[CosmoKramer]

Check out Access help for the Find Unmatched Query Wizard, this might be what you're looking for......

 

[WildHare]

JJohns, this is a standard outer join - show me the EMPLOYEES who DO NOT have a match in the CLASSES table. Simple once you picture it.

Bring both tables in to the query grid.
Join based on the Employee ID
Set the Query type to ALL EMPLOYEES and ANY CLASSES that match.

Bring both EMPID fields in to the grid, as well as name, and whatever else you want from the EMPLOYEE table.

Set the CRITERIA for the CLASSES.EMPID to &quot;IS NULL&quot;

Run it.

You will get ALL of those EMPLOYEES who DO NOT (is null) have a match in the CLASSES table.

I have a FAQ on joins lying around here somewhere as well.

Jim

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

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

 

[JJOHNS]

Wildhare, you've given me the answer. Except for one thing, however. I'll replace the Classes table in your example with the query that gives all the renewal dates = 2004. That will take all the renewal dates from 2003 and before out of the equation.

Cosmo, I've heard of unmatched queries, but never used one. I tried to find an example in a book with no luck. I'll take your advice and try using Access Help. Never even occurred to me.

Thanks