Counting Unique Values within a Group


Scott Albright

I have a report that lists students by grade and the amount of money donated
by that student's family. If the parents are divorced, the student's name is
listed twice, once for each parent. I want to count only the unique student
record in each grade. DCount is giving me the count for the whole school
regardless of what group detail i put it in. How do I count only the unique
records within each grouping?

Thanks in advance.



Duane Hookom

I would attempt to create a totals query that groups by whatever and counts
something. Add this totals query to your report's record source and join on
the whatever so the counts something is then available in the report.



John Spencer

This gets asked reasonably often, so here is a new article:
ECount() - an extended DCount()

ECount() takes the same arguments as DCount(), but an extra flag you can set
to retrieve the distinct count.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

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