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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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:
Click to expand...
tom = 7 (2+2+2+1)
tim = 6 (1+2+2+1)
james = 0

How do I do this.
I appreciate the help.
Paul
 
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.
 
Back
Top