Results 1 to 6 of 6
  1. #1
    jbuckner72 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    12

    One query to count AND count distinct

    I am doing a totals query and I want to count one field normally and another field based on distinct values. I am relatively comfortable with SQL and using Access for O365. In the example below, I want to count how many rows there are by Age and then want to the number of distinct PersonIDs within that Age.

    Source Table: Events
    Age PersonID
    0-4 1
    0-4 2
    0-4 3
    0-4 1
    5-9 4
    5-9 5
    5-9 4

    What I want my resulting query to look like:


    Age AgeCount UniquePersonCount
    0-4 4 3
    5-9 3 2

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    You need to use 2 queries, the first to get the distinct values and the second to do the count. These can be combined into one if necessary

    The first wry (qry1)

    Select distinct age, id from mytable

    The second

    Select age, count(Id) from qry1 group by age

  3. #3
    jbuckner72 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    12
    Thanks. I was hoping there was a more direct solution but this will work.

    Quote Originally Posted by Ajax View Post
    You need to use 2 queries, the first to get the distinct values and the second to do the count. These can be combined into one if necessary

    The first wry (qry1)

    Select distinct age, id from mytable

    The second

    Select age, count(Id) from qry1 group by age

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    As I said, you can combine them

    Select age, count(Id) from (Select distinct age, id from mytable) as Qry group by age

  5. #5
    orange's Avatar
    orange is online now Moderator
    Windows 10 Office 365
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with Ajax's solution --- there is no Count Distinct in Access.

    SQL COUNT(DISTINCT column_name) Syntax

    The COUNT(DISTINCT column_name) function returns the number of distinct values of the specified column:
    SELECT COUNT(DISTINCT column_name) FROM table_name;

    Note: COUNT(DISTINCT) works with ORACLE and Microsoft SQL Server, but not with Microsoft Access.


    You can create a user defined function

    Code:
    ' ----------------------------------------------------------------
    ' Procedure Name: CountDistinct
    ' Purpose: To get Count of Distinct Values in a given table
    ' Procedure Kind: Function
    ' Procedure Access: Public
    ' Parameter YourField (String): Field to be Counted
    ' Parameter YourTable (String): Table with Field
    ' Return Type: Integer
    ' Author: Jack
    ' Date: 21-Feb-21
    ' ----------------------------------------------------------------
    Function fCountDistinct(YourField As String, YourTable As String) As Integer
    10        On Error GoTo fCountDistinct_Error
              Dim SQL As String
              Dim rst As DAO.Recordset
    20        SQL = "SELECT Count(" & YourField & ") as DistCount" _
                  & " from (select distinct " & YourField _
                  & " from " & YourTable & ");"
    30        Set rst = CurrentDb.OpenRecordset(SQL)
    40        fCountDistinct = rst!DistCount  'return the Count of Distinct values
    50        rst.Close
              
    60        On Error GoTo 0
    fCountDistinct_Exit:
    70        Exit Function
    
    fCountDistinct_Error:
    80        MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fCountDistinct, line " & Erl & "."
    90        GoTo fCountDistinct_Exit
    End Function
    Sample query:

    SELECT fCountDistinct("City","CustomerA") AS demo
    FROM CustomerA
    GROUP BY fCountDistinct("City","CustomerA");

    demo
    69

  6. #6
    jbuckner72 is offline Novice
    Windows 10 Access 2016
    Join Date
    Dec 2019
    Posts
    12
    Super helpful. Going to mess around with out a user defined function!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 07-29-2020, 06:05 AM
  2. Replies: 0
    Last Post: 02-11-2020, 08:12 PM
  3. Replies: 4
    Last Post: 01-15-2019, 11:50 AM
  4. Distinct count in a query
    By Fabdav in forum Access
    Replies: 5
    Last Post: 09-14-2011, 04:53 PM
  5. Count distinct records in parameterized query
    By SilverSN95 in forum Access
    Replies: 5
    Last Post: 07-27-2010, 09:31 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums