Calculation across fields

[maxyoyo]

Hey there,



I need a solution for the following problem:
For example:

............F1 F2 F3
Rec1 ....4 ..2 ..5
Rec2 ....3 ..3 ..1
Rec3 ....1 ..2 ..1

Now, I want to know how many 1s are there in Rec3 the answer is obviously 2 but I want a field(F4, for e.g) that calculates F1+F2+F3 showing all their 1s 2s and 3s.

A solution I found was...... F4=IIf([F1],1,0)+IIf([F2],1,0)+IIf([F3],1,0)

But I have over 50 Fields that contain 1s and 2s and I can't count them all by using this formula as it will be tremendously long and access will reject it.

So I need another formula(or function) that will look up for the 1s across multiple fields and show the total count for each record in a new field.

Tried 'look up' function but achieved nothing~

Any help/suggestion/advice are highly appreciated.

[June7]

Access might accept an expression with 50 fields, if the field names are extremely short. I think expression length is limited by number of characters, not number of fields.

Alternative would be a custom function that opens a recordset and cycles through the fields and increments a variable. Complicated and can be very slow.

I suspect this data structure is not normalized and that is the real issue.

[orange]

I agree that your table structure may be part of the issue.

[maxyoyo]

Thanks for the reply, I will look forward to fixing my table structure.