count unique records

[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.

 

[Jeff Boyce]

Lynn

Use a query <!g!>. Then use the result of the query in your report.

 

[Guest]

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?

 

[Jeff Boyce]

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.