# help me with the query in ms access

i have a table:
Rec      SA                      ISVC                     Desc                                     Type
1      48686882      8363304                    AIR CONDITIONING                    ELECTRIC
2      48686882      8363304                    SPACE HEATER                        ELECTRIC
3      48686882      8363304                    WATER HEATER                        GAS
4      46666662      8555554                    AIR CONDITIONING                    ELECTRIC
5      46666662      8555554                    SPACE HEATER                        ELECTRIC
6      46666662      8555554                    WATER HEATER                        ELECTRIC

there are six records.
two groups :
first three and bottom three
I need to count ISVC where all measures are electric. the number is not 8363304, because one of the measures is gas.
so the right answer should be 1 , because for ISVX 8555554 every single measure is electric

could you help me to write a query to get
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

President / OwnerCommented:
Select Count(*) as AllElectricCount FROM (SELECT Table1.ISVC, Sum(IIf([Type]="ELECTRIC",0,1)) AS Expr1
FROM Table1
GROUP BY Table1.ISVC
HAVING (Sum(IIf([Type]="ELECTRIC",0,1)))=0)
CIOCommented:
Try with a simple query:

``````Select
SA, ISVC
From
YourTable
Where
Count(*) = Abs(Sum([Type] = 'ELECTRIC'))
Group By
SA, ISVC
``````
Owner, Developing Solutions LLCCommented:
This should do it.

SELECT SA , ISVC, SUM(1) as Count, Sum(iif(Type = "Electric", 1, 0))
FROM yourTable
GROUP BY SA, ISVC
HAVING Sum(1) = Sum(iif(Type = "Electric", 1, 0))

Experts Exchange Solution brought to you by