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
Roman FAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Jim Dettman (Microsoft MVP/ EE MVE)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)
Gustav BrockCIOCommented:
Try with a simple query:

Select   
    SA, ISVC
From
    YourTable
Where
    Count(*) = Abs(Sum([Type] = 'ELECTRIC'))
Group By
    SA, ISVC

Open in new window

Dale FyeOwner, 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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Roman FAuthor Commented:
thank you all
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Did you try that?   Because I don't believe Dale's SQL gives you what you want.

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.