query to count yes/no fields amounts

  • Thread starter Thread starter davethewelder
  • Start date Start date
D

davethewelder

Hi, I have a table with 20 Yes/No fields which is populated. I have created
a query which sums each field and populates a report. The problem is it
counts the number ok but displays the total as a negative no.

Any ideas to rectify this?

Any help greatly appreciated.

Davie
 
Checkboxes store a blank as a zero and a check as a -one. So put a field in
your query that says IIF([mycheckboxfield]=-1,"yes","no")
 
Milton, thanks for the quick response. The calculation does change the -1 to
Yes in the query but then I cannot sum the result. If I filter on "Yes" on
one field I cannot sum the other fields and would require 20 Queries. Any
idea how to count the yesses?

Davie

golfinray said:
Checkboxes store a blank as a zero and a check as a -one. So put a field in
your query that says IIF([mycheckboxfield]=-1,"yes","no")
--
Milton Purdy
ACCESS
State of Arkansas


davethewelder said:
Hi, I have a table with 20 Yes/No fields which is populated. I have created
a query which sums each field and populates a report. The problem is it
counts the number ok but displays the total as a negative no.

Any ideas to rectify this?

Any help greatly appreciated.

Davie
Click to expand...
Click to expand...
 
davethewelder said:
Hi, I have a table with 20 Yes/No fields which is populated. I have created
a query which sums each field and populates a report. The problem is it
counts the number ok but displays the total as a negative no.
Click to expand...


You can use a varity of methods:

=Abs(Sum(field))

=-Sum(field)

=Sum(IIf(field,1,0))

=Sum(IIf(field,1,Null))
 
I think you need to create 2 total columns:
Count([Yes: field]), Criteria = -1
Count([No: field]), Criteria = 0
 
20 Yes/No fields sounds a bit un-normalized to me. I could be wrong but I
think you might be storing data values in field names.
 
Duane, the 20 fields are for storing whether a patient has taken a certain
option. They may take more than one option but it is only held in one table
with one record per patient.

Davie
 
That's about what I suspected. Your field names most likely are data values
that identify the option.

What happens if you want to add a 21st or 22nd option? How about when you
need to remove an option?

I would create a related table of Patient Options. Each option for each
patient would create a record in the related table. This would allow any
number of options without having to ever change table structures, forms,
reports,... Just my 2 cents.
 
Duane, I get your point now. I will certainly do that with my next design. I
am unfortunately to close to delivery date to change things.

Thanks for the advice.

Davie
 
Back
Top