convert "yes" to '2' then add all fields

[Guest]

Hi All,
This should be simple....
To make my question clear, I put a demo table here:
http://tallahasseech.org/accessquestion.htm

- I want to assign the number '2' to each yes and assign '1' to a "no"

- I want to then add up all the numbers in the fields for each record (person)

So the answers for my demo would be:

tom = 7 (2+2+2+1)
tim = 6 (1+2+2+1)
james = 0

How do I do this.
I appreciate the help.
Paul

 

[Douglas J. Steele]

Are Yes and No text fields, or is that a Boolean field that's formatted as
Yes/No, rather than True/False?

In either case, you can create a query that has a computed field in it: the
details will be a little different, though.

Create the Select query and drag whatever fields you care about into the
grid. Pick an empty column in the grid, put your cursor in the Field row of
that column and hit Shift-F2 to bring up the Zoom box.

In the Zoom box, type:

Total: IIf(Len(Trim([tall] & "")) = 0, 0, IIf([tall] ="yes", 2, 1)) +
IIf(Len(Trim([strong] & "")) = 0, 0, IIf([strong] = "yes", 2, 1)) +
IIf(Len(Trim([heavy] & "")) = 0, 0, IIf([heavy] ="yes", 2, 1)) +
IIf(Len(Trim([thin] &"")), 0 , IIf([thin] ="yes", 2, 1))

if they're text, and

Total: IIf(IsNull([tall]), 0, IIf([tall], 2, 1)) + IIf(IsNull([strong]), 0,
IIf([strong], 2, 1)) + IIf(IsNull([heavy]), 0, IIf([heavy], 2, 1)) +
IIf(IsNull([thin]), 0 , IIf([thin], 2, 1))

if they're boolean.

 

[Guest]

much appreciated, Doug