How do I total results in different fields in Access?

[Melissa]

I have a listing of test answers: 1 = Yes 0 = No.

How can I total the 1's in a query? I've tried writing a 'sum' query. The
results I get are '00101' instead of '2' .

Thanks for your help.

 

[Rob]

Ok. If you are doing this in a report, create a new text box and in the
textbox (not the label) write this:

=Sum[(NameofField)]

what that does is add together all the responses in the field. Hope that
helps.

Rob

 

[Duane Hookom]

What is your "sum" expression? This looks like you are concatenating fields
across rather than a single field from many records.

What is the data type of your field?

 

[Melissa]

Dear Duane,

I am concatenating fields. I'm thinking that the data type may be the
problem -

I'm getting the numbers from a query that i IIf statements: switching letter
grades - A, B, C, D - for numbers - 1 if the answer is correct, 0 if not.
I'm thinking that the numbers won't add because Access is seeing these as
text, not numbers. Is there some way around this?

Thanks for the help - Melissa

 

[Melissa]

Rob -

I did that and got a result of '00100110' - not a sum of '3'.

Melissa

Ok. If you are doing this in a report, create a new text box and in the
textbox (not the label) write this:

=Sum[(NameofField)]

what that does is add together all the responses in the field. Hope that
helps.

Rob

I have a listing of test answers: 1 = Yes 0 = No.

How can I total the 1's in a query? I've tried writing a 'sum' query. The
results I get are '00101' instead of '2' .

Thanks for your help.

 

[Duane Hookom]

I think you need to:
-Normalize your table so you don't add values across fields
-create lookup table that has the grades and their associated numeric values
-use the values from the lookup table to total across records, not fields.

Otherwise, you can wrap your IIf(...) in Val( IIf(...) )