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 quie...
2016
access
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.  

 

Resources