Sum Total Yesses from several different Fields

[kimmer7389]

I thought my Access skills were better than this. Glad this site exists to get me unstuck. Sometimes my brain pauses.

I have a report that lists several different fields and the counts of yesses and nos. It looks like this.

Field Yes Count No Count
Field 1 2 3
Field 2 4 1
Field 3 5 0

How do I easily Get a total Count of Yesses?
I have tried to write an expression that looks something like this...

=Sum(IIf([Field 1]+[Field 2]+[Fied 3]="Yes",0))

but that didn't work. I tried commas in place of the plus signs and that didn't work either.

Would VBA code work better?

Thanks
Kim

 

[dhookom]

What are your actual field names and sample data? Why do you list fields down the left in a column?

This is wrong in a couple ways:

=Sum(IIf([Field 1]+[Field 2]+[Fied 3]="Yes",0))

- [Fied 3] is not spelled correctly.
- Typically "+" is used to add numbers and yet you compare to a string "Yes".



Duane
Hook'D on Access
MS Access MVP

 

[dhookom]

After reading your other thread, I think your table structure might be un-normalized. You should generally aggregate values across records, not across fields. It really looks like you have a field name that should be a data value.

Duane
Hook'D on Access
MS Access MVP

 

[kimmer7389]

I am just using "Field" as an example. I didn't post my actual field names.

I can't really post my actual field names and sample data. The example I posted is exatly what the report looks like.

Fields are listed down the left side with yes and no counts.

The fields are listed down the left because that is how we need the report to display. There are 12 fields all together and they are Yes/No. I have to display the Yes and No counts for each one.

Now we need to total all the Yesses and Nos. That is the part I am stuck on.

 

[kimmer7389]

The other post is for a totally different database and a totally different report.

My tables are normalized.

 

[kimmer7389]

This is the structure of the table, form and report...

Table
Question 1 Yes/NO
Question 2 Yes/NO

Form
Did you do this? Checkbox

Did you do that? Checkbox


Report

Question Yes Counts No Couts
Did you do this? 1 3
Did you do that? 2 1

I need Total Yesses Total Nos

 

[dhookom]

IMO, the table structure is wrong. If you need to add another "Did you do..." then you would need to add another field as well as controls on forms/reports, columns in queries, and change your expressions. Your current structure also causes you to start threads such as this rather than simple Sum() or Count() expression is queries.

In your "Report", how do you derive YesCounts and NoCounts? Are these in a group header or footer section or are they calculated in a query?


Duane
Hook'D on Access
MS Access MVP

 

[kimmer7389]

How do you recommend the table structure be set up?

Something like..

Field Data Type
Question Yes/No
Comments Memo
Corrective Action Memo

And then add the questions into the table?

There are also comments for each question and Corrective Actions.

I inherited the table but I think it is early enough in the game I could change it.

I am currently calculating Yes/No counts on the report in separate text boxes using expressions. The hard way.

 

[dhookom]

A more normalized solution is At Your Survey. You could add fields for comments and corrective actions. If there are multiple comments and corrective actions per question, then I would consider splitting them out into a related table.

Duane
Hook'D on Access
MS Access MVP

 

[kimmer7389]

Thanks. I will take a look at the example.

There is only one comment and corrective action per question.

 

[MajP]

Although you should fix your database as instructed.

How to count any value across any amount of fields

Public Function getCountOfValue(val As Variant, ParamArray flds()) As Integer
  Dim fld As Variant
  For Each fld In flds
    If Not IsNull(fld) Then
      If val = fld Then
        getCountOfValue = getCountOfValue + 1
      End If
    End If
  Next fld
End Function

example:

SELECT 
 tblTwo.field1, 
 tblTwo.field2, 
 tblTwo.field3, 
 tblTwo.field4, 
 getCountOfValue(-1,[field1],[field2],[field3],[field4]) AS CountYes,  
 getCountOfValue(0,[field1],[field2],[field3],[field4]) AS CountNo
FROM tblTwo;