SELECT query many to one with a count 1

[gwoman]

Hi ....
I am attempting to create a select query that has a count by FAC_ID and a many to one record type situation.
My table has records with API_NBR (can have duplicates) and there can be (and are) more than one record(s) that have a unique FAC_ID and have various STATE_TYPE_CODES. I need to select out by API_NBR with a FAC_ID count > 1 where their STATE_TYPE_CODES are all = "PA".

This is what I have ... but it is not working ....

SELECT CMPL_DMN.API_NBR, Count(CMPL_DMN.CMPL_FAC_ID) AS CountOfCMPL_FAC_ID
FROM CMPL_DMN
WHERE ((([CountOfCMPL_FAC_ID])>1) AND ((CMPL_DMN.CMPL_STATE_TYPE_CODE)="PA"))
GROUP BY CMPL_DMN.API_NBR;

I either get a syntax error ... or it is showing me all the records that have a TYPE_CODE of "PA"

Any help would be greatly appreciated ...

Thanks in advance ...

Regards ...
gwoman

 

[PHV]

SELECT CMPL_DMN.API_NBR, Count(CMPL_DMN.CMPL_FAC_ID) AS CountOfCMPL_FAC_ID
FROM CMPL_DMN
WHERE CMPL_DMN.CMPL_STATE_TYPE_CODE="PA"
GROUP BY CMPL_DMN.API_NBR
HAVING Count(CMPL_DMN.CMPL_FAC_ID)>1

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[gwoman]

HAVING" .... duh!
Thanks so much PHV ... you're the best!

regards...
gwoman