count across fields 1

[l0knezmonstir]

I have a table with 9 text fields that are either yes, no, or null. I need to count how many records have 1 no, 2 no's..etc. Is there a way to do this. Im using access 2000.
-Marty Loftus

 

[scriverb]

Here is an example of how to do this. Just update it with your table name and field names:
SELECT Table5.RecordId, IIf([YourTableName]![Field1]="No",1,0)+IIf([YourTableName]![Field2]="No",1,0)+IIf([YourTableName]![Field3]="No",1,0)+IIf([YourTableName]![Field4]="No",1,0)+ IIf([YourTableName]![Field5]="No",1,0)+IIf([YourTableName]![Field6]="No",1,0)+IIf([YourTableName]![Field7]="No",1,0)+IIf([YourTableName]![Field8]="No",1,0) + +IIf([YourTableName]![Field9]="No",1,0)AS CountOfNoAnswers
FROM YourTableName;

Now I followed your instructions and searched for a text NO rather than a Boolean (No or False or -1). If these fields are not text then we just need to change the format of the expression slightly.
Bob Scriver

 

[scriverb]

Sorry, missed one Table5 replacement. That was my test table.
SELECT [YourTableName].[RecordId], IIf([YourTableName]![Field1]="No",1,0)+IIf([YourTableName]![Field2]="No",1,0)+IIf([YourTableName]![Field3]="No",1,0)+IIf([YourTableName]![Field4]="No",1,0)+ IIf([YourTableName]![Field5]="No",1,0)+IIf([YourTableName]![Field6]="No",1,0)+IIf([YourTableName]![Field7]="No",1,0)+IIf([YourTableName]![Field8]="No",1,0) + +IIf([YourTableName]![Field9]="No",1,0)AS CountOfNoAnswers
FROM YourTableName;
Bob Scriver

 

[l0knezmonstir]

This is my modified version:
SELECT [t_orderlights].[schoolId], IIf([t_orderlights]![ontrack]="No",1,0)+IIf([t_orderlights]![studenteditor]="No",1,0)+IIf([t_orderlights]![correctversion]="No",1,0)+IIf([t_orderlights]![digitalcontent]="No",1,0)+IIf([t_orderlights]![unitssatisfied]="No",1,0)+IIf(t_orderlights]![merchandisingplan]="No",1,0)+IIf([t_orderlights]![makingpages]="No",1,0)+IIf([t_orderlights]![artwork]="No",1,0)+IIf([t_orderlights]![deliveryplan]="No",1,0) AS CountOfNoAnswers FROM t_orderlights;

when I run it, I get a query syntax error that starts at the first IIF and cuts off in the middle of merchandising plan. Im pretty sure i didnt miss anything. What could be wrong?

-Marty

 

[scriverb]

You deleted a left square bracket. See Red [. Just copy and paste. I fixed it.
SELECT [t_orderlights].[schoolId], IIf([t_orderlights]![ontrack]="No",1,0)+IIf([t_orderlights]![studenteditor]="No",1,0)+IIf([t_orderlights]![correctversion]="No",1,0)+IIf([t_orderlights]![digitalcontent]="No",1,0)+IIf([t_orderlights]![unitssatisfied]="No",1,0)+IIf([t_orderlights]![merchandisingplan]="No",1,0)+IIf([t_orderlights]![makingpages]="No",1,0)+IIf([t_orderlights]![artwork]="No",1,0)+IIf([t_orderlights]![deliveryplan]="No",1,0) AS CountOfNoAnswers FROM t_orderlights;

Let me know if you have any further problems.
Bob Scriver

 

[l0knezmonstir]

ouch. Thanks man, I looked over it like 12 times and didnt catch that. Works perfectly thanks.
-Marty

 

[scriverb]

Glad to be able to help. Thanks for the star. Bob Scriver