Forum Discussion

JoeCavasin's avatar
JoeCavasin
Brass Contributor
May 18, 2025

Distinct Count Subquery, MS Access

Need some help in getting a Distinct Count result in this query.  For context, i work in healthcare and of course an not share the actual data behind the query.  My goal is to modify the current quiery - which returns total charges by account financial class (medicaid, commercial, medicare, sellf pay) and account date within a given month (post date, in the query), but add on a distinct count of hospital accounts in the grouping.  Current SQL below, all data lives in the ez_hosp_chg_v. table.  End goal is to return a distinct count of the bold/red data point: "HOSP_ACCT_ID"

SELECT ez_hosp_chg_v.POST_DATE, ez_hosp_chg_v.TX_FIN_CLASS, ez_hosp_chg_v.ACCT_CLASS, Sum(ez_hosp_chg_v.ORIG_AMT) AS SumOfORIG_AMT, ez_hosp_chg_v.PRIMARY_PLAN_NAME, ez_hosp_chg_v.HOSP_ACCT_ID

FROM ez_hosp_chg_v

GROUP BY ez_hosp_chg_v.POST_DATE, ez_hosp_chg_v.TX_FIN_CLASS, ez_hosp_chg_v.ACCT_CLASS, ez_hosp_chg_v.PRIMARY_PLAN_NAME, ez_hosp_chg_v.HOSP_ACCT_ID

HAVING (((ez_hosp_chg_v.POST_DATE) Between [enter start date] And [enter end date]));

 

I've attempted incorporating a subquery like this distinct count example, but can't get the syntax correct in my version:

source: https://www.access-programmers.co.uk/forums/threads/count-unique-values-in-a-query.327614/

 

 Any help or pointers are appreciated.

-Joe

2016
access

4 Replies

  • Gerrit_Viehmann's avatar
    Gerrit_Viehmann
    Brass Contributor
    May 21, 2025

    I would try this:

    SELECT q1.POST_DATE
    , q1.TX_FIN_CLASS
    , q1.ACCT_CLASS
    , q1.PRIMARY_PLAN_NAME
    , Sum(q1.SumPerHoAcc) AS SumOfORIG_AMT
    , Sum(q1.NumberOfDistinctHospitalAccounts) AS CountOfDistinctHospitalAccounts
FROM (
    SELECT ez_hosp_chg_v.POST_DATE
        , ez_hosp_chg_v.TX_FIN_CLASS
        , ez_hosp_chg_v.ACCT_CLASS
        , ez_hosp_chg_v.PRIMARY_PLAN_NAME
        , ez_hosp_chg_v.HOSP_ACCT_ID
        , Sum(ez_hosp_chg_v.ORIG_AMT) AS SumPerHoAcc
        , 1 AS NumberOfDistinctHospitalAccounts
    FROM ez_hosp_chg_v
    WHERE ez_hosp_chg_v.POST_DATE BETWEEN [Enter start date]
            AND [Enter end date]
    GROUP BY ez_hosp_chg_v.POST_DATE
        , ez_hosp_chg_v.TX_FIN_CLASS
        , ez_hosp_chg_v.ACCT_CLASS
        , ez_hosp_chg_v.PRIMARY_PLAN_NAME
        , ez_hosp_chg_v.HOSP_ACCT_ID
    ) AS q1
GROUP BY q1.POST_DATE
    , q1.TX_FIN_CLASS
    , q1.ACCT_CLASS
    , q1.PRIMARY_PLAN_NAME;

     

  • Kidd_Ip's avatar
    Kidd_Ip
    MVP
    May 19, 2025

    How about this:

     

    SELECT 
    ez_hosp_chg_v.POST_DATE, 
    ez_hosp_chg_v.TX_FIN_CLASS, 
    ez_hosp_chg_v.ACCT_CLASS, 
    Sum(ez_hosp_chg_v.ORIG_AMT) AS SumOfORIG_AMT, 
    ez_hosp_chg_v.PRIMARY_PLAN_NAME, 
    (SELECT COUNT(*) FROM (SELECT DISTINCT HOSP_ACCT_ID FROM ez_hosp_chg_v AS sub WHERE sub.POST_DATE = ez_hosp_chg_v.POST_DATE AND sub.TX_FIN_CLASS = ez_hosp_chg_v.TX_FIN_CLASS AND sub.ACCT_CLASS = ez_hosp_chg_v.ACCT_CLASS AND sub.PRIMARY_PLAN_NAME = ez_hosp_chg_v.PRIMARY_PLAN_NAME)) AS DistinctAccountCount
FROM ez_hosp_chg_v
GROUP BY 
    ez_hosp_chg_v.POST_DATE, 
    ez_hosp_chg_v.TX_FIN_CLASS, 
    ez_hosp_chg_v.ACCT_CLASS, 
    ez_hosp_chg_v.PRIMARY_PLAN_NAME;

     

    • JoeCavasin's avatar
      JoeCavasin
      Brass Contributor
      May 19, 2025

      Thanks Kidd_Ip, i'll give it a shot tomorrow when i'm on a more stable connection.  tonight it starts to run but then Access crashes within a few seconds.  

       

  • ProcessIT's avatar
    ProcessIT
    Copper Contributor
    May 18, 2025

    Hi Joe,

    If I'm correctly following what you ask, then I thnk you are simply missing the "COUNT() function.

    Try this:

    SELECT ez_hosp_chg_v.POST_DATE, ez_hosp_chg_v.TX_FIN_CLASS, ez_hosp_chg_v.ACCT_CLASS, Sum(ez_hosp_chg_v.ORIG_AMT) AS SumOfORIG_AMT, 
      ez_hosp_chg_v.PRIMARY_PLAN_NAME, COUNT(ez_hosp_chg_v.HOSP_ACCT_ID) as HospCount
FROM ez_hosp_chg_v
GROUP BY ez_hosp_chg_v.POST_DATE, ez_hosp_chg_v.TX_FIN_CLASS, ez_hosp_chg_v.ACCT_CLASS, ez_hosp_chg_v.PRIMARY_PLAN_NAME, 
     ez_hosp_chg_v.HOSP_ACCT_ID
HAVING (((ez_hosp_chg_v.POST_DATE) Between [enter start date] And [enter end date]));

     

Resources