Need help with a DISTINCT and COUNT issue 1

[ehicks727]

Hello, here's some code below...

SELECT TblLOB.rpt_cust_name AS [Customer Name], COUNT(TblLOB.state_cd) AS [State Count], <snipped a lot of other fields>
FROM TblLOB INNER JOIN TblMemCnt ON TblLOB.id = TblMemCnt.id
GROUP BY TblLOB.rpt_cust_name;

What I'm trying to get is the second column is supposed to be a DISTINCT count of state_cd, not just a regular count. This works in MySQL by just using COUNT(DISTINCT state_cd), but I don't know how to translate this into Access. I'm getting an error message.

Any help is greatly appreciated.

 

[PHV]

A starting point:
SELECT D.rpt_cust_name AS [Customer Name], Count(*) AS [State Count]
FROM (SELECT DISTINCT A.rpt_cust_name, A.state_cd FROM TblLOB) AS D
GROUP BY D.rpt_cust_name

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ehicks727]

Here's what I changed it to, following your recommendations, as best I understood them... I got an error message saying "syntax error in JOIN"

I'm going to include all the fields this time because I realize that may change the equation a little, because I'm linking to another table for all those extra fields... sorry it's kind of long.

SELECT D.rpt_cust_name AS [Customer Name], COUNT(D.state_cd) AS St, Sum(TblMemCnt.memasohmo) AS MEM_ASO_HMO, Sum(TblMemCnt.memasoppo) AS MEM_ASO_PPO, Sum(TblMemCnt.memhmo) AS MEM_HMO, Sum(TblMemCnt.memind) AS MEM_IND, Sum(TblMemCnt.memmcd) AS MEM_MCD, Sum(TblMemCnt.memppo) AS MEM_PPO, Sum(TblMemCnt.memrsk) AS MEM_RSK, Sum(TblMemCnt.memsup) AS MEM_SUP, Sum(TblMemCnt.subasohmo) AS SUB_ASO_HMO, Sum(TblMemCnt.subasoppo) AS SUB_ASO_PPO, Sum(TblMemCnt.subhmo) AS SUB_HMO, Sum(TblMemCnt.subind) AS SUB_IND, Sum(TblMemCnt.submcd) AS SUB_MCD, Sum(TblMemCnt.subppo) AS SUB_PPO, Sum(TblMemCnt.subrsk) AS SUB_RSK, Sum(TblMemCnt.subsup) AS SUB_SUP
FROM (SELECT DISTINCT TblLOB.rpt_cust_name, TblLOB.state_cd FROM tblLOB) AS D
INNER JOIN TblMemCnt ON TblLOB.id=TblMemCnt.id
GROUP BY TblLOB.rpt_cust_name;

PH, did I just implement what you suggested wrong?

thanks,

 

[PHV]

Create a query named, say, qrySumMemCnt:
SELECT TblLOB.rpt_cust_name, Sum(TblMemCnt.memasohmo), Sum(TblMemCnt.memasoppo), Sum(TblMemCnt.memhmo), Sum(TblMemCnt.memind), Sum(TblMemCnt.memmcd), Sum(TblMemCnt.memppo), Sum(TblMemCnt.memrsk), Sum(TblMemCnt.memsup), Sum(TblMemCnt.subasohmo), Sum(TblMemCnt.subasoppo), Sum(TblMemCnt.subhmo), Sum(TblMemCnt.subind), Sum(TblMemCnt.submcd), Sum(TblMemCnt.subppo), Sum(TblMemCnt.subrsk), Sum(TblMemCnt.subsup)
FROM TblLOB INNER JOIN TblMemCnt ON TblLOB.id = TblMemCnt.id
GROUP BY TblLOB.rpt_cust_name;

Create a query named, say, qryDistinctState:
SELECT DISTINCT rpt_cust_name, state_cd
FROM TblLOB;

And now your query:
SELECT qrySumMemCnt.rpt_cust_name AS [Customer Name], Count(*) AS St, First(qrySumMemCnt.memasohmo) AS MEM_ASO_HMO, First(qrySumMemCnt.memasoppo) AS MEM_ASO_PPO, First(qrySumMemCnt.memhmo) AS MEM_HMO, First(qrySumMemCnt.memind) AS MEM_IND, First(qrySumMemCnt.memmcd) AS MEM_MCD, First(qrySumMemCnt.memppo) AS MEM_PPO, First(qrySumMemCnt.memrsk) AS MEM_RSK, First(qrySumMemCnt.memsup) AS MEM_SUP, First(qrySumMemCnt.subasohmo) AS SUB_ASO_HMO, First(qrySumMemCnt.subasoppo) AS SUB_ASO_PPO, First(qrySumMemCnt.subhmo) AS SUB_HMO, First(qrySumMemCnt.subind) AS SUB_IND, First(qrySumMemCnt.submcd) AS SUB_MCD, First(qrySumMemCnt.subppo) AS SUB_PPO, First(qrySumMemCnt.subrsk) AS SUB_RSK, First(qrySumMemCnt.subsup) AS SUB_SUP
FROM qrySumMemCnt INNER JOIN qryDistinctState ON qrySumMemCnt.rpt_cust_name = qryDistinctState.rpt_cust_name
GROUP BY qrySumMemCnt.rpt_cust_name;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[ehicks727]

Wow! that appears to have done the trick... as always PH, you are a genius! My deepest thanks.

For anyone's future reference who may look at this, I had to add 'AS xxxxx' after all each of the 'Sum(TblMemCnt.xxxxx)' in qrySumMemCnt, but other than that, worked smoothly.

It's too bad Access queries must go through all this trouble... MySQL did it in one statement, one query, but I'm forced to work with Access periodically, which is why I hang out on Tek Tips!

thanks again,
E

 

[PHV]

MySQL did it in one statement
Lots of real RDBMS don't lacks the COUNT(DISTINCT ...) aggregate function.