Add totals of record type to a form footer.

[JSchumacher]

Hi.

I've created a form with ~60 fields. 47 of them are combo boxes with 'yes',
'no' and 'na' options available.

I'm trying to create a series of text boxes at the bottom of the form to
show the counts of each selection (to be used later in determining a final
score, so at the footer the user would see something like

Total Questions; 74
Total Not Applicable; 24
Total Yes; 45
Total No; 5

I've so far tried playing around with the expression builder using the
'count' feature but can't quite seem to get the syntax right.

Advice appreciated.

 

[Allen Browne]

If these are text fields, you can count the number of Yes answers like this
(assuming a field is named Question1):
=Sum(IIf([Question1]="yes", 1, 0))

To try to count all the yes answers across 60 fields, you would need to
create such a text box for each field, and then another text box to sum the
total, e.g.:
=[Text0] + [Text1] + [Text2] + ...

In the end, the problem is that this is completely the wrong way to design a
table in a relational database. For an explanation, see:
Don't use Yes/No fields to store preferences
at:
http://allenbrowne.com/casu-23.html

Once you understand that, if you want an example of how to design a
questionnaire database, Duane Hookom has one called At Your Survey here:
http://www.rogersaccesslibrary.com/...82687a4d-z74a-81474293-f33z519f-e6b9d9a3.html