sum multiple yes/no checkbox fields

  • Thread starter Thread starter TxBlueEyes
  • Start date Start date
T

TxBlueEyes

Okay, here'e the issue...

I have a report that want to have in the Report header the sum of all of the
following fields that are "Yes" ... they are all Yes/No fields.... These are
used for tracking what Order Type call was that a Call Center Agent was
monitored On... And my goal is to be able to give the grand Total of all
Types (FYI there can be more than one order type per record, so I can't just
use record total... I am already using the IE: =Sum(IIf([Order Type Field
Name],1,0)) for tracking the Total Order Type sum based on the Specific
Grouping Criteria, which is working great... However, I have not been able to
get the overall total of all the Order Type fields answered with "Yes" in one
report Field.. Can anyone assist or recommend how I can accomplish this???
Thanks for any assitance you can provide... TxBlueEyes
Below are the Yes/No Fields:
Order
WISMO
Billing
REFUND
Product Inquiry
 
It would help if you provided some sample records and desired output. If this
is the total for one of the fields, you should be able to add antoher
=Sum(IIf([Order Type Field Name],1,0)) + Sum(IIf([Order Type Anther Field
Name],1,0))

I don't care much for multiple check boxes rather than a normalized table
structure with a junction table.
 
Assuming that you want to sum the number of boxes checked, you should be
able to use the following expression

=Abs(Sum([Order] + [WISMO] + [Billing] + [Refund] + [Product Inquiry]))

Yes/no fields store 0 (for false) and -1 (for true). Adding up all the
yes values returns a negative number which you then SUM. Abs strips off
the negative sign.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
John - that did itg... fantastic... thanks so much... that was so simple...
DUH! Awesome.... have a fantastic day....

John Spencer said:
Assuming that you want to sum the number of boxes checked, you should be
able to use the following expression

=Abs(Sum([Order] + [WISMO] + [Billing] + [Refund] + [Product Inquiry]))

Yes/no fields store 0 (for false) and -1 (for true). Adding up all the
yes values returns a negative number which you then SUM. Abs strips off
the negative sign.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Okay, here'e the issue...

I have a report that want to have in the Report header the sum of all of the
following fields that are "Yes" ... they are all Yes/No fields.... These are
used for tracking what Order Type call was that a Call Center Agent was
monitored On... And my goal is to be able to give the grand Total of all
Types (FYI there can be more than one order type per record, so I can't just
use record total... I am already using the IE: =Sum(IIf([Order Type Field
Name],1,0)) for tracking the Total Order Type sum based on the Specific
Grouping Criteria, which is working great... However, I have not been able to
get the overall total of all the Order Type fields answered with "Yes" in one
report Field.. Can anyone assist or recommend how I can accomplish this???
Thanks for any assitance you can provide... TxBlueEyes
Below are the Yes/No Fields:
Order
WISMO
Billing
REFUND
Product Inquiry
Click to expand...
Click to expand...
 
As a teacher, am trying to create a form with yes/no fields for attendance
(tardies and absences). I need to add the number of absences checked per
student (per record). Using abs(sum) totals all the checkmarks for the
entire database. Is there a simple statement I can use (i'm not a
programmer) that results in a "per record" total?
John Spencer said:
Assuming that you want to sum the number of boxes checked, you should be
able to use the following expression

=Abs(Sum([Order] + [WISMO] + [Billing] + [Refund] + [Product Inquiry]))

Yes/no fields store 0 (for false) and -1 (for true). Adding up all the
yes values returns a negative number which you then SUM. Abs strips off
the negative sign.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Okay, here'e the issue...

I have a report that want to have in the Report header the sum of all of the
following fields that are "Yes" ... they are all Yes/No fields.... These are
used for tracking what Order Type call was that a Call Center Agent was
monitored On... And my goal is to be able to give the grand Total of all
Types (FYI there can be more than one order type per record, so I can't just
use record total... I am already using the IE: =Sum(IIf([Order Type Field
Name],1,0)) for tracking the Total Order Type sum based on the Specific
Grouping Criteria, which is working great... However, I have not been able to
get the overall total of all the Order Type fields answered with "Yes" in one
report Field.. Can anyone assist or recommend how I can accomplish this???
Thanks for any assitance you can provide... TxBlueEyes
Below are the Yes/No Fields:
Order
WISMO
Billing
REFUND
Product Inquiry
Click to expand...
Click to expand...
 
What are your fields and how do you want to display the results? If you have
multiple yes/no fields, I expect the table structure is not normalized. For
instance fields with names like Mon, Tue, Wed,... or other repeating/sequence
values are most likely wrong. You should generally need to sum values across
multiple records, not multiple fields.

--
Duane Hookom
Microsoft Access MVP


Eloy said:
As a teacher, am trying to create a form with yes/no fields for attendance
(tardies and absences). I need to add the number of absences checked per
student (per record). Using abs(sum) totals all the checkmarks for the
entire database. Is there a simple statement I can use (i'm not a
programmer) that results in a "per record" total?
John Spencer said:
Assuming that you want to sum the number of boxes checked, you should be
able to use the following expression

=Abs(Sum([Order] + [WISMO] + [Billing] + [Refund] + [Product Inquiry]))

Yes/no fields store 0 (for false) and -1 (for true). Adding up all the
yes values returns a negative number which you then SUM. Abs strips off
the negative sign.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

Okay, here'e the issue...

I have a report that want to have in the Report header the sum of all of the
following fields that are "Yes" ... they are all Yes/No fields.... These are
used for tracking what Order Type call was that a Call Center Agent was
monitored On... And my goal is to be able to give the grand Total of all
Types (FYI there can be more than one order type per record, so I can't just
use record total... I am already using the IE: =Sum(IIf([Order Type Field
Name],1,0)) for tracking the Total Order Type sum based on the Specific
Grouping Criteria, which is working great... However, I have not been able to
get the overall total of all the Order Type fields answered with "Yes" in one
report Field.. Can anyone assist or recommend how I can accomplish this???
Thanks for any assitance you can provide... TxBlueEyes
Below are the Yes/No Fields:
Order
WISMO
Billing
REFUND
Product Inquiry
Click to expand...
Click to expand...
Click to expand...
 
Back
Top