Query with filter help

[SnowB1]

I have a form that I use a filter on to make my where statement for the query I have. The form has 3 drop downs: County, City, and State. Everything works great except when you only choose a state you should get the totals for just the state and instead you get a report by city. Here is my sql:

SELECT Sum(IIf([Homeless]="yes",1,0)) AS HomelessCnt, Sum(IIf([NFresident]="yes",1,0)) AS NFresidentCnt, Sum(IIf([Self]="yes",1,0)) AS SelfCnt, Sum(IIf([StaffBoard]="yes",1,0)) AS StaffBoardCnt, Sum(IIf([FamilyFriend]="yes",1,0)) AS FamilyFriendCnt, Sum(IIf([CommLarge]="yes",1,0)) AS CommLargeCnt, Sum(IIf([ServeProvide]="yes",1,0)) AS ServeProvideCnt, Sum(IIf([Physical]="yes",1,0)) AS PhysicalCnt, Sum(IIf([MentalEmo]="yes",1,0)) AS MentalEmoCnt, Sum(IIf([Cognitive]="yes",1,0)) AS CognitiveCnt, Sum(IIf([Vision]="yes",1,0)) AS VisionCnt, Sum(IIf([Hearing]="yes",1,0)) AS HearingCnt, Sum(IIf([Multiple]="yes",1,0)) AS MultipleCnt, Sum(IIf([AdvocacyServe]="yes",1,0)) AS AdvocacyServeCnt, Sum(IIf([AssistDevServe]="yes",1,0)) AS AssistDevServeCnt, Sum(IIf([ChildServe]="yes",1,0)) AS ChildServeCnt, Sum(IIf([CommServ]="yes",1,0)) AS CommServCnt, Sum(IIf([FamilyServe]="yes",1,0)) AS FamilyServeCnt, Sum(IIf([HousingServe]="yes",1,0)) AS HousingServeCnt, Sum(IIf([ILSkillServe]="yes",1,0)) AS ILSkillServeCnt, Sum(IIf([InformationServe]="yes",1,0)) AS InformationServeCnt, Sum(IIf([MentalRest]="yes",1,0)) AS MentalRestCnt, Sum(IIf([MobilityServe]="yes",1,0)) AS MobilityServeCnt, Sum(IIf([PeerServe]="yes",1,0)) AS PeerServeCnt, Sum(IIf([PersonalServe]="yes",1,0)) AS PersonalServeCnt, Sum(IIf([PhysicalRest]="yes",1,0)) AS PhysicalRestCnt, Sum(IIf([PreventiveServe]="yes",1,0)) AS PreventiveServeCnt, Sum(IIf([Prostheses]="yes",1,0)) AS ProsthesesCnt, Sum(IIf([RecServe]="yes",1,0)) AS RecServeCnt, Sum(IIf([RehabTech]="yes",1,0)) AS RehabTechCnt, Sum(IIf([CounselServe]="yes",1,0)) AS CounselServeCnt, Sum(IIf([Therapeutic]="yes",1,0)) AS TherapeuticCnt, Sum(IIf([TransportServe]="yes",1,0)) AS TransportServeCnt, Sum(IIf([YouthServe]="yes",1,0)) AS YouthServeCnt, Sum(IIf([VocationServe]="yes",1,0)) AS VocationServeCnt, Sum(IIf([OtherServe]="yes",1,0)) AS OtherServeCnt, Sum(IIf([Newsletter]="yes",1,0)) AS NewsletterCnt, Sum(IIf([EventFlyer]="yes",1,0)) AS EventFlyerCnt, Sum(IIf([VFIBrochure]="yes",1,0)) AS VFIBrochureCnt, Sum(IIf([WaiverInfo]="yes",1,0)) AS WaiverInfoCnt, Sum(IIf([Survey]="yes",1,0)) AS SurveyCnt, Sum(IIf([Other]="yes",1,0)) AS OtherCnt, Sum(IIf([Advocacy]="yes",1,0)) AS AdvocacyCnt, Sum(IIf([SkillTraining]="yes",1,0)) AS SkillTrainingCnt, Sum(IIf([PeerSupport]="yes",1,0)) AS PeerSupportCnt, Sum(IIf([Deinstitutionalization]="yes",1,0)) AS DeinstitutionalizationCnt, Sum(IIf([ReferralPAS]="yes",1,0)) AS ReferralPASCnt, Sum(IIf([ReferralVFI]="yes",1,0)) AS ReferralVFICnt, Avg(PersonalInfo.MinSpent) AS MinSpentAvg, Avg(PersonalInfo.CallerAge) AS CallerAgeAvg, PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState
FROM PersonalInfo
GROUP BY PersonalInfo.CallerCounty, PersonalInfo.CallerCity, PersonalInfo.CallerState;

I have tried to take out the group by and PersonalInfo.CallerCounty, ect. Any ideas would be greatly appreciated.
Thanks

 

[FoFa]

You need to either just pull County and City out of the query totally for a state rollup, or have them set to one single common value (like "ALL"). Since those fields pretty much do not have any relevance at that level, I would just remove them.
Same would be true for County rollup and City I would think.
At least that is what I think.

 

[SnowB1]

The only problem with that is that I would need to make a report and query for every different combination. I was hoping to do this through one query and one report.

 

[FoFa]

You can do that if you can change the City and Country (in your example) to say ALL instead of pulling the data from the table. This would be possible to by (thinking out loud) to use IIF in place of the county and city in a query that your query would be based off of (instead of a table) and having the IIF detect if it needs to return ALL or the DB field instead.

 

[SnowB1]

Thanks for your help, I'll give it a try.