count unique records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is a repost as I got no result with the previous suggestions
I have a report in which I want to summarise how many people are qualified.
the report lists the employeeid the qualification amongst other fields. I
want to count how many people in a particular location are qualified.
However, someone may be qualified in 2 qualifications, but I only want to
count them once.

For example
employeeid 01 is qualified in care 1
employeeid 01 is qualified in management1
employeeid 02 is qualified in care 1
employee id 03 is qualified in care 1
employeeid 03 is qualified in management1

I want the calculation to return the number 3 ie 3 employees qualified, no
matter how many qualifications they have.

How do I do this

a previous suggestion was to use the uniquevalues in the query, but the
count is done in a report and the records would not be unique.

Any other suggestions gratefully received.
 
Yes, but the records in the query are not unique. the point is that someone
can have 2 different qualifications which needs to be a criterion in the
query. so their 2 records listed in the query would be
status=complete, award = management 4, employeeID=10 AND
status=complete, award = care 4, employeeID=10

I want to count employeeID=10 only once
What am I missing? How do I do this?
 
Lynn

Open a new query.

Add EmployeeID from the table listing "qualifications" two times.

Click on the greek sigma toolbar button (Totals query).

Change the second GroupBy under EmployeeID to Count.

You get a list of EmployeeIDs (the GroupBy), and the count of how many times
each EmployeeID shows up in the table (the Count).

This presumes your "qualifications" table is somewhat normalized.
 
Back
Top