How do I get a distinct count of multiple records in a group?

K

KRB

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 Position Employee Count 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 per Position

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Distinct Counts for multiple records in a group.. 1
How do I get a count of records in a group? 5
Calculate leave time in a query 4
How do I count the number of group breaks? 1
How to get a count of items in a Select Distinct query? 2
how do i perform a Count(Distinct) query for 2 different column va 1
How do I count individual records in the Detailed section of a Rep 8
How Do I do This? 4

Top