Count Distinct Number of Email Addresses in the Results of a Query in MS Access

Question

I have a query in MS Access to return multiple columns from multiple tables for a user-specified period of time. This is for a training registration database, and often times, people take multiple classes during a given time period. I need to get a count of the distinct number of people who were trained during the given time frame, but I can't figure out any way to do this in Access. Here is the query I'm starting with:

SELECT 
  UsersTABLE.FirstName, 
  UsersTABLE.LastName, 
  UsersTABLE.EmailAddress, 
  UsersTABLE.Department, 
  UsersTABLE.ActiveUser, 
  ClassSessionsTABLE.CourseName, 
  ClassSessionsTABLE.StartTime, 
  ActivityTABLE.Registered, 
  ActivityTABLE.Attended
FROM 
  ClassSessionsTABLE 
INNER JOIN 
  (UsersTABLE 
    INNER JOIN ActivityTABLE 
     ON UsersTABLE.[UserID] = ActivityTABLE.[UserID]) 
     ON ClassSessionsTABLE.ClassSessionID = ActivityTABLE.ClassSessionID
WHERE 
  (((ClassSessionsTABLE.StartTime) 
      Between [Early Date in MM/DD/YYYY hh:mm:ss AM/PM format] 
      And [Late Date in MM/DD/YYYY hh:mm:ss AM/PM format]) 
  AND ((ActivityTABLE.Registered)=True))
ORDER BY UsersTABLE.LastName;

From these query results, I want to count the distinct number of email addresses (UsersTABLE.EmailAddress) returned in the query and return that number, along with the rest of the query information, in a report. I am using email address because I have that set as requiring a unique value in the table.

I've seen several suggestions online for getting a distinct count of something, but when I try them, I get either the total number of email addresses in the UsersTable, or I get a count of the total number of records returned by the query.

Can anyone help me get a count of the distinct email addresses returned by the above query?

Answer 1

I do not know of any way to incorporate it in your query directly, but the following query should get you the number of unique email addresses using the same conditions and everything from your orginal query:

SELECT COUNT(*) AS NumEMails FROM 
(SELECT
  DISTINCT UsersTABLE.EmailAddress
FROM 
  ClassSessionsTABLE 
INNER JOIN 
  (UsersTABLE 
    INNER JOIN ActivityTABLE 
     ON UsersTABLE.[UserID] = ActivityTABLE.[UserID]) 
     ON ClassSessionsTABLE.ClassSessionID = ActivityTABLE.ClassSessionID
WHERE 
  (((ClassSessionsTABLE.StartTime) 
      Between [Early Date in MM/DD/YYYY hh:mm:ss AM/PM format] 
      And [Late Date in MM/DD/YYYY hh:mm:ss AM/PM format]) 
  AND ((ActivityTABLE.Registered)=True))) AS Mails;

Answer 2

I just want to show how to add luk2302's subquery to the existing query:

SELECT UsersTABLE.FirstName, UsersTABLE.LastName, UsersTABLE.EmailAddress, UsersTABLE.Department, UsersTABLE.ActiveUser, ClassSessionsTABLE.CourseName, ClassSessionsTABLE.StartTime, ActivityTABLE.Registered, ActivityTABLE.Attended
(SELECT COUNT(*) AS NumEMails FROM 
(SELECT
  DISTINCT UsersTABLE.EmailAddress
FROM 
  ClassSessionsTABLE 
INNER JOIN 
  (UsersTABLE 
    INNER JOIN ActivityTABLE 
     ON UsersTABLE.[UserID] = ActivityTABLE.[UserID]) 
     ON ClassSessionsTABLE.ClassSessionID = ActivityTABLE.ClassSessionID
WHERE 
  (((ClassSessionsTABLE.StartTime) 
      Between [Early Date in MM/DD/YYYY hh:mm:ss AM/PM format] 
      And [Late Date in MM/DD/YYYY hh:mm:ss AM/PM format]) 
  AND ((ActivityTABLE.Registered)=True))) AS Mails;) AS Expr1
FROM ClassSessionsTABLE INNER JOIN (UsersTABLE INNER JOIN ActivityTABLE ON UsersTABLE.[UserID] = ActivityTABLE.[UserID]) ON ClassSessionsTABLE.ClassSessionID = ActivityTABLE.ClassSessionID
WHERE (((ClassSessionsTABLE.StartTime) Between [Early Date in MM/DD/YYYY hh:mm:ss AM/PM format] And [Late Date in MM/DD/YYYY hh:mm:ss AM/PM format]) AND ((ActivityTABLE.Registered)=True))
ORDER BY UsersTABLE.LastName;