Expression too long?

[SnowB1]

Here is my sql statement that I want to get the total number of "yes" in each field. Each field is a text field.

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


It says:
"This expression is type incorrectly, or it is too complex to be evalutated. Try Simplifying the expression by assigning parts of the expression to variables."

Does this mean I can not do it all in one query, or how do I assign variables. Sorry I am kinda new to access. Thanks for the help.

Bobby

 

[chacal]

Hi,

I'm not sure how you could make your query, but I know that you cannot use Sum() the way you want to use it. You should use Count(ColumnName).

Your query could be something like :

Select Count(ColumnName1), count(columnName2)...
From PersonalInfo
WHERE (((PersonalInfo.CallerCounty)=[Forms]![CityStateCountyForm]![CmbCallerCounty]) AND ((PersonalInfo.CallerCity)=[Forms]![CityStateCountyForm]![CmbCallerCity]) AND (PersonalInfo.CallerState)=[Forms]![CityStateCountyForm]![CmbCallerState]))
AND (Count(ColumnName1) = 'Yes' Or count(columnName2) = 'Yes' Or...);

Try to build your query inside Access Query Design View. Sometimes it's easier this way.

Hope this could help...

 

[SnowB1]

Thanks for the comment. I ended up getting it to work using Sum(IIF([fieldname]="yes",1,0)) as QueryFieldName. Thanks again

Bobby

 

[mresann]

You have a table that is a prime candidate for normalization. Instead of horizontal expansion regarding survey answers, consider creating an "answer table". A query linking the answer table to the contact table is much easier to create.

In addition, changing the fields from text fields to yes/no (boolean) fields should be considered. The Iif statements can be eliminated in favor of Abs functions, as a boolean field (graphically a check box) value is either -1 or 0; Abs(-1) = +1, cutting considerable time from the query.