count distinct

Question

I am very new to MS Access and I am having trouble counting distinct records.  Each person in my database is tracked by their SSN.  Every time that a person is enrolled in a class, a row in the databse is populated with their SSN (SSN), the class provider (ORG), and the date of enrollment (ENROLLDATE). A person may enroll in many classes, so their SSN is entered multiple times, followed by different enrollment dates.  How do I count each enrolled person only once (by their SSN)?  Ideally, I want the query to return a list of the class providers with a count of every person that has enrolled at least one time.  For example:

Organization A enrolled 5 people (5 SSNs)

Organization B enrolled 10 people (10 SSNs)

Please help!!

Answer 1

Excellent, thank you so much! If I wanted to add another field, such as quarter, how would I add that? The question is: per organization, how many unduplicated people (SSNs) enrolled in a class each quarter?  The result would look like:

Q1          Q2       Q3

Organization A          5              7          6

Organization B          9             12        2

I really appreciate your help!

Answer 2

Count the rows per Org returned by a subquery which returns a distinct set of Org/SSN values, e.g.

SELECT Org, COUNT(*) AS StudentCount

FROM (SELECT DISTINCT Org, SSN

           FROM Enrolments) AS DistinctStudents

GROUP BY Org;

Answer 3

It looks like Ken just forgot the Quarter in the Group By.

GROUP BY Org**, Quarter;**

Any field that doesn't have an aggregate function (SUM, COUNT, MAX...) needs to be included in the Group By.

Answer 4

Hi Ken,

I have tried it a few times, but I get this error message:

"You tried to execute a query that does not include a sepcified expression 'Quarter' as part of an aggregate function."

Do you have any thoughts?

Thank you!!

Answer 5

You'd start by including the quarter in the result table:

SELECT Org, Quarter, COUNT(*) AS StudentCount

FROM (SELECT DISTINCT Org, DATEPART("q",EnrollDate) As Quarter, SSN

           FROM Enrolments) AS DistinctStudents

GROUP BY Org;

Then either convert it to a crosstab query, or use a report with a multi-column subreport to give the columnar layout.