Distinct Counts for multiple records in a group..

[Guest]

I’m having problems getting a distinct count. I have 4 tables I am using. I
have an Site table( School Name) , position table, number table( employee
IDs) and reason table (sick leave reason).
I want to get a count of employees per position. The catch is I’m also
joining on a table called reason which is sick leave ( because I will have to
get a count of absences as well), so I have multiple records per employee per
position. I just want a distinct count of Employees per position. Example
of how my report looks now:


Site Positio Employee Reason
Test School
Asst. Principal
01
Sick Leave
01 Sick Leave
01 Vacation Leave
4th grad teacher
02 Vacation Leave
03 Sick Leave
03 Sick Leave
PE Teacher
05 Professional Development
05 Sick Leave

I want it to look like this:

Site Position Employee Count Total Absences

Test School
Asst Principal 1 3
4th grad teacher 2 3
PE Teacher 1 2

I have tried the Count (*) and the text box with control source =1. I don’t
know what else to try


Was this post helpful to you?

 

[Duane Hookom]

The easiest solution might be to remove the "reason" table from the report
and place it in a subreport. This gives you unique employee records.

I could be more definite if I knew more about your table structures since
"reason table" doesn't suggest that it stores an employee ID.