query to count yes/no fields amounts

[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

 

[golfinray]

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")

 

[KARL DEWEY]

Apply function ABS to the sum --
=Abs(Sum(....))

 

[davethewelder]

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

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

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

 

[Marshall Barton]

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.

You can use a varity of methods:

=Abs(Sum(field))

=-Sum(field)

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

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

 

[Gordon]

I think you need to create 2 total columns:
Count([Yes: field]), Criteria = -1
Count([No: field]), Criteria = 0

 

[davethewelder]

Marshall, this worked a treat. Thanks for the help.

Davie

 

[davethewelder]

Karl, this worked a treat. Thanks for taking the time to help.

Davie

 

[Duane Hookom]

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.

 

[davethewelder]

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

 

[Duane Hookom]

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.

 

[davethewelder]

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