Prev: Iteration through a multi-Select List box
Next: Delete code competing with BeforeUpdate validation
From: JSchumacher on 2 Jun 2010 22:26
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.
From: Allen Browne on 2 Jun 2010 23:27
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/forum/hookom-duane_forum4&SID=82687a4d-z74a-81474293-f33z519f-e6b9d9a3.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"JSchumacher" <JSchumacher(a)discussions.microsoft.com> wrote in message
news:74C9406F-30B6-41CD-910D-88EC6508434E(a)microsoft.com...
> 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.

From: Marshall Barton on 3 Jun 2010 10:56
JSchumacher wrote:
>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.


60 fields is a LOT, 47 related fields is a serious problem.
(If the fields were not related, you would not be able to
total them.) A normalized table can only have a primary key
(one or more unrelated fields) and other fields that depend
ONLY on the primary key. The fact that you can add up
fields with the same value is a violation of the rules of
normalization.

The only way you can do what you ask is to use a ridiculous
expressions like =-(a1="na" + a2="na" + . . . . + a47="na")

A normalized structure would have all the questions/answers
in a separate table, one q/a per record, displayed in a
continuous subform. This way you can count the different
values by using an aggregate function such as:
=Count(Iff(a = "na", 1, Null))

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: Iteration through a multi-Select List box
Next: Delete code competing with BeforeUpdate validation