help me with the query in ms access

Roman F
Roman F used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
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)
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Try with a simple query:

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

Open in new window

Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
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))

Author

Commented:
thank you all
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Did you try that?   Because I don't believe Dale's SQL gives you what you want.

Jim.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial