Count Checkboxes

[zufie]

I have to create a QA report regarding callers calling into a phone
hotline.

The report consists of many checkboxes such as:

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

I thought it best to use the Caller ID to help me group responses?

How best do I go about counting the respective checkboxes?

Thanks!

John

[paii, Ron]

Re: Count Checkboxes


"zufie" <john.marruffo@ illinois.govwro te in message
news:1b1b7f2f-7e9a-4ecc-9d8c-7fde3aeb708e@p2 5g2000hsf.googl egroups.com...

I have to create a QA report regarding callers calling into a phone
hotline.
>
The report consists of many checkboxes such as:
>
Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know
>
I thought it best to use the Caller ID to help me group responses?
>
How best do I go about counting the respective checkboxes?
>
Thanks!
>
John
>
>

1) How did you organize your table?
Are all the checkboxes in one row with the Caller ID like a spreadsheet?
CallerID R1 R2 R3 ........ R50

Or did you normalize and put one checkbox per row?
CallerID QuestionID Response

2) What are you counting?
Number of Yes responses per Caller ID
Number of times a response was true, false or NULL for all Caller IDs

[zufie]

Re: Count Checkboxes

On Sep 17, 3:07 pm, "paii, Ron" <n...@no.comwro te:

"zufie" <john.marru...@ illinois.govwro te in message
>
news:1b1b7f2f-7e9a-4ecc-9d8c-7fde3aeb708e@p2 5g2000hsf.googl egroups.com...
>
>
>
>
>

I have to create a QA report regarding callers calling into a phone
hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John

>
1) How did you organize your table?
Are all the checkboxes in one row with the Caller ID like a spreadsheet? Yes
CallerID   R1  R2  R3 ........ R50
>
Or did you normalize and put one checkbox per row? No
CallerID QuestionID  Response
>
2) What are you counting?
Number of Yes responses per Caller ID Yes
Number of times a response was true,  false or NULL for all Caller IDs-Hide quoted text -

Counting the number of times a response was Yes, No, Don't Know

I then have to calculate the percent of callers that responded Yes,
No, Don't Know

Thanks,
John

- Show quoted text -

[zufie]

Re: Count Checkboxes

On Sep 17, 3:18 pm, zufie <john.marru...@ illinois.govwro te:

On Sep 17, 3:07 pm, "paii, Ron" <n...@no.comwro te:
>
>
>

"zufie" <john.marru...@ illinois.govwro te in message

>

news:1b1b7f2f-7e9a-4ecc-9d8c-7fde3aeb708e@p2 5g2000hsf.googl egroups.com....

>

I have to create a QA report regarding callers calling into a phone
hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John

>

1) How did you organize your table?
Are all the checkboxes in one row with the Caller ID like a spreadsheet?  Yes
CallerID   R1  R2  R3 ........ R50

>

Or did you normalize and put one checkbox per row? No
CallerID QuestionID  Response

>

2) What are you counting?
Number of Yes responses per Caller ID Yes
Number of times a response was true,  false or NULL for all Caller IDs- Hide quoted text -

>
Counting the number of times a response was Yes, No, Don't Know
>
I then have to calculate the percent of callers that responded Yes,
No, Don't Know
>
Thanks,
John
>
>
>

- Show quoted text -- Hide quoted text -

>
- Show quoted text -- Hide quoted text -
>
- Show quoted text -

How did you organize your table?
Are all the checkboxes in one row with the Caller ID like a
spreadsheet? Yes, all the checkboxes in one row with the Caller ID
like a spreadsheet
CallerID R1 R2 R3 ........ R50

Or did you normalize and put one checkbox per row? I didn't normalize.
That is, there is more than one checkbox per row
CallerID QuestionID Response

Thanks,
John

[zufie]

Re: Count Checkboxes

On Sep 17, 3:21 pm, zufie <john.marru...@ illinois.govwro te:

On Sep 17, 3:18 pm, zufie <john.marru...@ illinois.govwro te:
>
>
>
>
>

On Sep 17, 3:07 pm, "paii, Ron" <n...@no.comwro te:

>

"zufie" <john.marru...@ illinois.govwro te in message

>

>news:1b1b7f2 f-7e9a-4ecc-9d8c-7fde3aeb708e@p2 5g2000hsf.googl egroups.com....

>

I have to create a QA report regarding callers calling into a phone
hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John

>

1) How did you organize your table?
Are all the checkboxes in one row with the Caller ID like a spreadsheet?  Yes
CallerID   R1  R2  R3 ........ R50

>

Or did you normalize and put one checkbox per row? No
CallerID QuestionID  Response

>

2) What are you counting?
Number of Yes responses per Caller ID Yes
Number of times a response was true,  false or NULL for all Caller IDs- Hide quoted text -

>

Counting the number of times a response was Yes, No, Don't Know

>

I then have to calculate the percent of callers that responded Yes,
No, Don't Know

>

Thanks,
John

>

- Show quoted text -- Hide quoted text -

>

- Show quoted text -- Hide quoted text -

>

- Show quoted text -

>
How did you organize your table?
Are all the checkboxes in one row with the Caller ID like a
spreadsheet? Yes, all the checkboxes in one row with the Caller ID
like a spreadsheet
CallerID   R1  R2  R3 ........ R50
>
Or did you normalize and put one checkbox per row? I didn't normalize.
That is, there is more than one checkbox per row
CallerID QuestionID  Response
>
Thanks,
John- Hide quoted text -
>
- Show quoted text -

That is, the checkboxes for Yes, No, Don't Know for a particular
question are listed on the same row in the datasheet view (spreadsheet
view).

Thanks,
John

[paii, Ron]

Re: Count Checkboxes


"zufie" <john.marruffo@ illinois.govwro te in message
news:65d837b2-d01d-412e-a264-e72ea1492b4a@l4 3g2000hsh.googl egroups.com...
On Sep 17, 3:21 pm, zufie <john.marru...@ illinois.govwro te:

On Sep 17, 3:18 pm, zufie <john.marru...@ illinois.govwro te:
>
>
>
>
>

On Sep 17, 3:07 pm, "paii, Ron" <n...@no.comwro te:

>

"zufie" <john.marru...@ illinois.govwro te in message

>

>news:1b1b7f2 f-7e9a-4ecc-9d8c-7fde3aeb708e@p2 5g2000hsf.googl egroups.com...
>

I have to create a QA report regarding callers calling into a phone
hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency?

1Week/2Weeks/3

or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John

>

1) How did you organize your table?
Are all the checkboxes in one row with the Caller ID like a

spreadsheet? Yes

CallerID R1 R2 R3 ........ R50

>

Or did you normalize and put one checkbox per row? No
CallerID QuestionID Response

>

2) What are you counting?
Number of Yes responses per Caller ID Yes
Number of times a response was true, false or NULL for all Caller IDs-

Hide quoted text -

>

Counting the number of times a response was Yes, No, Don't Know

>

I then have to calculate the percent of callers that responded Yes,
No, Don't Know

>

Thanks,
John

>

- Show quoted text -- Hide quoted text -

>

- Show quoted text -- Hide quoted text -

>

- Show quoted text -

>
How did you organize your table?
Are all the checkboxes in one row with the Caller ID like a
spreadsheet? Yes, all the checkboxes in one row with the Caller ID
like a spreadsheet
CallerID R1 R2 R3 ........ R50
>
Or did you normalize and put one checkbox per row? I didn't normalize.
That is, there is more than one checkbox per row
CallerID QuestionID Response
>
Thanks,
John- Hide quoted text -
>
- Show quoted text -

That is, the checkboxes for Yes, No, Don't Know for a particular
question are listed on the same row in the datasheet view (spreadsheet
view).

Thanks,
John

Is there more then 1 question per row?
Please post the table structure

Try a summation query to get percent of times a question was answered true.

[Carlos Nunes-Ueno]

Re: Count Checkboxes

If what you need is a total for the Yes/No fields in one row, you can use
the IIF function in combination with the Sum function to give a total.
It would be something like this:

=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))

IIF has three arguments: 1) the expression to evaluate, 2) the result to
return if true, and 3) the result to return if false). In this case,
each IIF evaluates to 1 if the field is true, i.e. the box is checked,
and to 0 if it is not. Sum adds them together to return your result.

Otherwise, if what you need is a total of the Yes/Nos for the whole
table, you can put the function above into an aggregate query and sum it.
The function will add the Yes/Nos in each row and the query will sum them
into one total.

If neither of those is what you're looking for, give us some more detail
on what you're dealing with and what you're wanting to accomplish.

Hope this helps,

Carlos

zufie <john.marruffo@ illinois.govwro te in news:1b1b7f2f-7e9a-4ecc-9d8c-
7fde3aeb708e@p2 5g2000hsf.googl egroups.com:

I have to create a QA report regarding callers calling into a phone
hotline.
>
The report consists of many checkboxes such as:
>
Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know
>
I thought it best to use the Caller ID to help me group responses?
>
How best do I go about counting the respective checkboxes?
>
Thanks!
>
John
>
>

[zufie]

Re: Count Checkboxes

On Sep 17, 5:45 pm, "Carlos Nunes-Ueno" <sulla...@athot maildot.com>
wrote:

If what you need is a total for the Yes/No fields in one row, you can use
the IIF function in combination with the Sum function to give a total.  
It would be something like this:
>
=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))
>
IIF has three arguments: 1) the expression to evaluate, 2) the result to
return if true, and 3) the result to return if false).  In this case,
each IIF evaluates to 1 if the field is true, i.e. the box is checked,
and to 0 if it is not.  Sum adds them together to return your result.
>
Otherwise, if what you need is a total of the Yes/Nos for the whole
table, you can put the function above into an aggregate query and sum it. 
The function will add the Yes/Nos in each row and the query will sum them
into one total.
>
If neither of those is what you're looking for, give us some more detail
on what you're dealing with and what you're wanting to accomplish.
>
Hope this helps,
>
Carlos
>
zufie <john.marru...@ illinois.govwro te in news:1b1b7f2f-7e9a-4ecc-9d8c-
7fde3aeb7...@p2 5g2000hsf.googl egroups.com:
>
>
>

I have to create a QA report regarding callers calling into a phone
hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John- Hide quoted text -

>
- Show quoted text -

I need to count the total number of times each of the following three
checkbox responses was given:
1)Yes
2)No
3)Don't Know

I thought it best to use the Caller ID to group the responses?

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses:
1)Yes
2)No
3)Don't Know

If that, is what you meant, then we are on the same sheet of music.

If not, I hope my above message made it clearer what I meant?

Thanks!,
John

[zufie]

Re: Count Checkboxes

On Sep 18, 8:18 am, zufie <john.marru...@ illinois.govwro te:

On Sep 17, 5:45 pm, "Carlos Nunes-Ueno" <sulla...@athot maildot.com>
wrote:
>
>
>
>
>

If what you need is a total for the Yes/No fields in one row, you can use
the IIF function in combination with the Sum function to give a total.  
It would be something like this:

>

=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))

>

IIF has three arguments: 1) the expression to evaluate, 2) the result to
return if true, and 3) the result to return if false).  In this case,
each IIF evaluates to 1 if the field is true, i.e. the box is checked,
and to 0 if it is not.  Sum adds them together to return your result.

>

Otherwise, if what you need is a total of the Yes/Nos for the whole
table, you can put the function above into an aggregate query and sum it.  
The function will add the Yes/Nos in each row and the query will sum them
into one total.

>

If neither of those is what you're looking for, give us some more detail
on what you're dealing with and what you're wanting to accomplish.

>

Hope this helps,

>

Carlos

>

zufie <john.marru...@ illinois.govwro te in news:1b1b7f2f-7e9a-4ecc-9d8c-
7fde3aeb7...@p2 5g2000hsf.googl egroups.com:

>

I have to create a QA report regarding callers calling into a phone
hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John- Hide quoted text -

>

- Show quoted text -

>
I need to count the total number of times each of the following three
checkbox responses was given:
1)Yes
2)No
3)Don't Know
>
I thought it best to use the Caller ID to group the responses?
>
Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses:
1)Yes
2)No
3)Don't Know
>
If that, is what you meant, then we are on the same sheet of music.
>
If not, I hope my above message made it clearer what I meant?
>
Thanks!,
John- Hide quoted text -
>
- Show quoted text -

SORRY, I meant CHECKED:

I need to count the total number of times each of the following three
checkbox responses was CHECKED:
(That is, the total number of times the Yes check box was checked, the
total number of times the No check box was checked, the total number
of times the Don't Know check box was checked). I thought it best to
use the Caller ID to group these responses?

1)Yes
2)No
3)Don't Know

=============== =============== =============== =============== =============== ===
Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses (That is, the percent of
callers checking Yes, the percent of callers checking No, the
percent of callers checking Don't Know)
1)Yes
2)No
3)Don't Know

If that, is what you meant, then we are on the same sheet of music.

If not, I hope my above message made it clearer what I meant?

Thanks!,
John

[zufie]

Re: Count Checkboxes

On Sep 18, 8:25 am, zufie <john.marru...@ illinois.govwro te:

On Sep 18, 8:18 am, zufie <john.marru...@ illinois.govwro te:
>
>
>
>
>

On Sep 17, 5:45 pm, "Carlos Nunes-Ueno" <sulla...@athot maildot.com>
wrote:

>

If what you need is a total for the Yes/No fields in one row, you canuse
the IIF function in combination with the Sum function to give a total..  
It would be something like this:

>

=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))

>

IIF has three arguments: 1) the expression to evaluate, 2) the resultto
return if true, and 3) the result to return if false).  In this case,
each IIF evaluates to 1 if the field is true, i.e. the box is checked,
and to 0 if it is not.  Sum adds them together to return your result.

>

Otherwise, if what you need is a total of the Yes/Nos for the whole
table, you can put the function above into an aggregate query and sumit.  
The function will add the Yes/Nos in each row and the query will sum them
into one total.

>

If neither of those is what you're looking for, give us some more detail
on what you're dealing with and what you're wanting to accomplish.

>

Hope this helps,

>

Carlos

>

zufie <john.marru...@ illinois.govwro te in news:1b1b7f2f-7e9a-4ecc-9d8c-
7fde3aeb7...@p2 5g2000hsf.googl egroups.com:

>

I have to create a QA report regarding callers calling into a phone
hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John- Hide quoted text -

>

- Show quoted text -

>

I need to count the total number of times each of the following three
checkbox responses was given:
1)Yes
2)No
3)Don't Know

>

I thought it best to use the Caller ID to group the responses?

>

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses:
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>
SORRY, I meant CHECKED:
>
I need to count the total number of times each of the following three
checkbox responses was CHECKED:
(That is, the total number of times the Yes check box was checked, the
total number of times the No check box was checked, the total number
of times the Don't Know check box was checked). I thought it best to
use the Caller ID to group these responses?
>
1)Yes
2)No
3)Don't Know
>
=============== =============== =============== =============== =============== ===
Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses (That is, the percent of
callers checking Yes,  the percent of callers checking No,  the
percent of callers checking Don't Know)
1)Yes
2)No
3)Don't Know
>
If that, is what you meant, then we are on the same sheet of music.
>
If not, I hope my above message made it clearer what I meant?
>
Thanks!,
John- Hide quoted text -
>
- Show quoted text -

Ok, I now have a sum of the Yes checkbox responses & now have a sum of
the No checkbox responses.

However, the sums are negative numbers. How can I change these sums to
positive numbers?

I tried changing the sums to positive numbers in a query, in a form,
and in a report...all without success!

Here is my SQL code for my query:

SELECT DISTINCTROW Sum([IBCCP Referral Query].[MinOfAgencyCont actYes])
AS [Sum Of MinOfAgencyCont actYes], Sum([IBCCP Referral Query].
[MinOfAgencyCont actNo]) AS [Sum Of MinOfAgencyCont actNo]
FROM [IBCCP Referral Query];

Any suggestions as how to change these sums to positive numbers?

Thanks!
John

[zufie]

Re: Count Checkboxes

On Sep 18, 9:54 am, zufie <john.marru...@ illinois.govwro te:

On Sep 18, 8:25 am, zufie <john.marru...@ illinois.govwro te:
>
>
>
>
>

On Sep 18, 8:18 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 17, 5:45 pm, "Carlos Nunes-Ueno" <sulla...@athot maildot.com>
wrote:

>

If what you need is a total for the Yes/No fields in one row, you can use
the IIF function in combination with the Sum function to give a total.  
It would be something like this:

>

=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))

>

IIF has three arguments: 1) the expression to evaluate, 2) the result to
return if true, and 3) the result to return if false).  In this case,
each IIF evaluates to 1 if the field is true, i.e. the box is checked,
and to 0 if it is not.  Sum adds them together to return your result.

>

Otherwise, if what you need is a total of the Yes/Nos for the whole
table, you can put the function above into an aggregate query and sum it.  
The function will add the Yes/Nos in each row and the query will sum them
into one total.

>

If neither of those is what you're looking for, give us some more detail
on what you're dealing with and what you're wanting to accomplish.

>

Hope this helps,

>

Carlos

>

zufie <john.marru...@ illinois.govwro te in news:1b1b7f2f-7e9a-4ecc-9d8c-
7fde3aeb7...@p2 5g2000hsf.googl egroups.com:

>

I have to create a QA report regarding callers calling into a phone
hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency? 1Week/2Weeks/3
or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John- Hide quoted text -

>

- Show quoted text -

>

I need to count the total number of times each of the following three
checkbox responses was given:
1)Yes
2)No
3)Don't Know

>

I thought it best to use the Caller ID to group the responses?

>

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses:
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>

SORRY, I meant CHECKED:

>

I need to count the total number of times each of the following three
checkbox responses was CHECKED:
(That is, the total number of times the Yes check box was checked, the
total number of times the No check box was checked, the total number
of times the Don't Know check box was checked). I thought it best to
use the Caller ID to group these responses?

>

1)Yes
2)No
3)Don't Know

>

=============== =============== =============== =============== =============== ===
Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses (That is, the percent of
callers checking Yes,  the percent of callers checking No,  the
percent of callers checking Don't Know)
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>
Ok, I now have a sum of the Yes checkbox responses & now have a sum of
the No checkbox responses.
>
However, the sums are negative numbers. How can I change these sums to
positive numbers?
>
I tried changing the sums to positive numbers in a query, in a form,
and in a report...all without success!
>
Here is my SQL code for my query:
>
SELECT DISTINCTROW Sum([IBCCP Referral Query].[MinOfAgencyCont actYes])
AS [Sum Of MinOfAgencyCont actYes], Sum([IBCCP Referral Query].
[MinOfAgencyCont actNo]) AS [Sum Of MinOfAgencyCont actNo]
FROM [IBCCP Referral Query];
>
Any suggestions as how to change these sums to positive numbers?
>
Thanks!
John- Hide quoted text -
>
- Show quoted text -

Ok, I figured it out!

I used Abs(field).

Here is my sql code for one query:

SELECT Abs([MinOfAgencyCont actYes]) AS Expr1,
Abs([MinOfAgencyCont actNo]) AS Expr2
FROM [IBCCP Referral Query];

OR

I can use Abs(Sum([field])) which is safer to use, in case I have both
pos (+) and Neg (-) numbers (values).

Here is my my sql code for using Abs(Sum([field])) in a query:

SELECT Abs(Sum([MinOfAgencyCont actYes])) AS Expr1,
Abs(Sum([MinOfAgencyCont actNo])) AS Expr2
FROM [IBCCP Referral Query]
WITH OWNERACCESS OPTION;

I can also use Sum(Abs([field])) but this is less accurate as you
could have both pos (+) and Neg (-) (values).

[paii, Ron]

Re: Count Checkboxes


"zufie" <john.marruffo@ illinois.govwro te in message
news:da036b97-8bbd-4824-a04a-f8050cae44fe@k7 g2000hsd.google groups.com...
On Sep 18, 9:54 am, zufie <john.marru...@ illinois.govwro te:

On Sep 18, 8:25 am, zufie <john.marru...@ illinois.govwro te:
>
>
>
>
>

On Sep 18, 8:18 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 17, 5:45 pm, "Carlos Nunes-Ueno" <sulla...@athot maildot.com>
wrote:

>

If what you need is a total for the Yes/No fields in one row, you

can use

the IIF function in combination with the Sum function to give a

total.

It would be something like this:

>

=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))

>

IIF has three arguments: 1) the expression to evaluate, 2) the

result to

return if true, and 3) the result to return if false). In this case,
each IIF evaluates to 1 if the field is true, i.e. the box is

checked,

and to 0 if it is not. Sum adds them together to return your result.

>

Otherwise, if what you need is a total of the Yes/Nos for the whole
table, you can put the function above into an aggregate query and

sum it.

The function will add the Yes/Nos in each row and the query will sum

them

into one total.

>

If neither of those is what you're looking for, give us some more

detail

on what you're dealing with and what you're wanting to accomplish.

>

Hope this helps,

>

Carlos

>

zufie <john.marru...@ illinois.govwro te in

news:1b1b7f2f-7e9a-4ecc-9d8c-

7fde3aeb7...@p2 5g2000hsf.googl egroups.com:

>

I have to create a QA report regarding callers calling into a

phone

hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency?

1Week/2Weeks/3

or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John- Hide quoted text -

>

- Show quoted text -

>

I need to count the total number of times each of the following three
checkbox responses was given:
1)Yes
2)No
3)Don't Know

>

I thought it best to use the Caller ID to group the responses?

>

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses:
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>

SORRY, I meant CHECKED:

>

I need to count the total number of times each of the following three
checkbox responses was CHECKED:
(That is, the total number of times the Yes check box was checked, the
total number of times the No check box was checked, the total number
of times the Don't Know check box was checked). I thought it best to
use the Caller ID to group these responses?

>

1)Yes
2)No
3)Don't Know

>

=============== =============== =============== =============== ===============
===

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses (That is, the percent of
callers checking Yes, the percent of callers checking No, the
percent of callers checking Don't Know)
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>
Ok, I now have a sum of the Yes checkbox responses & now have a sum of
the No checkbox responses.
>
However, the sums are negative numbers. How can I change these sums to
positive numbers?
>
I tried changing the sums to positive numbers in a query, in a form,
and in a report...all without success!
>
Here is my SQL code for my query:
>
SELECT DISTINCTROW Sum([IBCCP Referral Query].[MinOfAgencyCont actYes])
AS [Sum Of MinOfAgencyCont actYes], Sum([IBCCP Referral Query].
[MinOfAgencyCont actNo]) AS [Sum Of MinOfAgencyCont actNo]
FROM [IBCCP Referral Query];
>
Any suggestions as how to change these sums to positive numbers?
>
Thanks!
John- Hide quoted text -
>
- Show quoted text -

>I can also use Sum(Abs([field])) but this is less accurate as you
>could have both pos (+) and Neg (-) (values).

If the field is true/false then you will not have an pos (+) numbers.
Checked = True = -1
Uncheck = False = 0

[zufie]

Re: Count Checkboxes

On Sep 18, 1:45 pm, "paii, Ron" <n...@no.comwro te:

"zufie" <john.marru...@ illinois.govwro te in message
>
news:da036b97-8bbd-4824-a04a-f8050cae44fe@k7 g2000hsd.google groups.com...
On Sep 18, 9:54 am, zufie <john.marru...@ illinois.govwro te:
>
>
>

On Sep 18, 8:25 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 18, 8:18 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 17, 5:45 pm, "Carlos Nunes-Ueno" <sulla...@athot maildot.com>
wrote:

>

If what you need is a total for the Yes/No fields in one row, you

can use

the IIF function in combination with the Sum function to give a

total.

It would be something like this:

>

=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))

>

IIF has three arguments: 1) the expression to evaluate, 2) the

result to

return if true, and 3) the result to return if false). In this case,
each IIF evaluates to 1 if the field is true, i.e. the box is

checked,

and to 0 if it is not. Sum adds them together to return your result.

>

Otherwise, if what you need is a total of the Yes/Nos for the whole
table, you can put the function above into an aggregate query and

sum it.

The function will add the Yes/Nos in each row and the query will sum

them

into one total.

>

If neither of those is what you're looking for, give us some more

detail

on what you're dealing with and what you're wanting to accomplish..

>

Hope this helps,

>

Carlos

>

zufie <john.marru...@ illinois.govwro te in

>
news:1b1b7f2f-7e9a-4ecc-9d8c-
>
>
>

7fde3aeb7...@p2 5g2000hsf.googl egroups.com:

>

I have to create a QA report regarding callers calling into a

phone

hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency?

1Week/2Weeks/3

or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John- Hide quoted text -

>

- Show quoted text -

>

I need to count the total number of times each of the following three
checkbox responses was given:
1)Yes
2)No
3)Don't Know

>

I thought it best to use the Caller ID to group the responses?

>

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses:
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>

SORRY, I meant CHECKED:

>

I need to count the total number of times each of the following three
checkbox responses was CHECKED:
(That is, the total number of times the Yes check box was checked, the
total number of times the No check box was checked, the total number
of times the Don't Know check box was checked). I thought it best to
use the Caller ID to group these responses?

>

1)Yes
2)No
3)Don't Know

>
=============== =============== =============== =============== ===============
===
>
>
>
>
>

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses (That is, the percent of
callers checking Yes, the percent of callers checking No, the
percent of callers checking Don't Know)
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>

Ok, I now have a sum of the Yes checkbox responses & now have a sum of
the No checkbox responses.

>

However, the sums are negative numbers. How can I change these sums to
positive numbers?

>

I tried changing the sums to positive numbers in a query, in a form,
and in a report...all without success!

>

Here is my SQL code for my query:

>

SELECT DISTINCTROW Sum([IBCCP Referral Query].[MinOfAgencyCont actYes])
AS [Sum Of MinOfAgencyCont actYes], Sum([IBCCP Referral Query].
[MinOfAgencyCont actNo]) AS [Sum Of MinOfAgencyCont actNo]
FROM [IBCCP Referral Query];

>

Any suggestions as how to change these sums to positive numbers?

>

Thanks!
John- Hide quoted text -

>

- Show quoted text -
I can also use Sum(Abs([field])) but this is less accurate as you
could have both pos (+) and Neg (-) (values).

>
If the field is true/false then you will not have an pos (+) numbers.
   Checked = True = -1
   Uncheck = False = 0- Hide quoted text -
>
- Show quoted text -- Hide quoted text -
>
- Show quoted text -- Hide quoted text -
>
- Show quoted text -

Yes, true. Thanks.

This is not the problem now.

My problem now is, now that I have used Sum(Abs([field])) to convert
my neg. (-) values to pos. (+) values how can I obtain the correct
percentage.

For example, here is my expression from my query trying to calculate
the correct percentage, in this case, 2/2+3 = .4*100 = 40%.

Expr18: (((Abs(Sum([MinOfAgencyCont actYes])))/
(Abs(Sum([MinOfAgencyCont actYes])))+
(Abs(Sum([MinOfAgencyCont actNo])))))

Expr18 returns 400% instead of 40% due to using Sum(Abs([field])).

How can I correct this?

I am so close to completing this report.

Thanks,

John

[zufie]

Re: Count Checkboxes

On Sep 18, 2:59 pm, zufie <john.marru...@ illinois.govwro te:

On Sep 18, 1:45 pm, "paii, Ron" <n...@no.comwro te:
>
>
>
>
>

"zufie" <john.marru...@ illinois.govwro te in message

>

news:da036b97-8bbd-4824-a04a-f8050cae44fe@k7 g2000hsd.google groups.com...
On Sep 18, 9:54 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 18, 8:25 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 18, 8:18 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 17, 5:45 pm, "Carlos Nunes-Ueno" <sulla...@athot maildot.com>
wrote:

>

If what you need is a total for the Yes/No fields in one row, you

can use

the IIF function in combination with the Sum function to give a

total.

It would be something like this:

>

=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))

>

IIF has three arguments: 1) the expression to evaluate, 2) the

result to

return if true, and 3) the result to return if false). In this case,
each IIF evaluates to 1 if the field is true, i.e. the box is

checked,

and to 0 if it is not. Sum adds them together to return your result.

>

Otherwise, if what you need is a total of the Yes/Nos for the whole
table, you can put the function above into an aggregate query and

sum it.

The function will add the Yes/Nos in each row and the query will sum

them

into one total.

>

If neither of those is what you're looking for, give us some more

detail

on what you're dealing with and what you're wanting to accomplish.

>

Hope this helps,

>

Carlos

>

zufie <john.marru...@ illinois.govwro te in

>

news:1b1b7f2f-7e9a-4ecc-9d8c-

>

7fde3aeb7...@p2 5g2000hsf.googl egroups.com:

>

I have to create a QA report regarding callers calling into a

phone

hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency?

1Week/2Weeks/3

or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John- Hide quoted text -

>

- Show quoted text -

>

I need to count the total number of times each of the following three
checkbox responses was given:
1)Yes
2)No
3)Don't Know

>

I thought it best to use the Caller ID to group the responses?

>

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses:
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>

SORRY, I meant CHECKED:

>

I need to count the total number of times each of the following three
checkbox responses was CHECKED:
(That is, the total number of times the Yes check box was checked, the
total number of times the No check box was checked, the total number
of times the Don't Know check box was checked). I thought it best to
use the Caller ID to group these responses?

>

1)Yes
2)No
3)Don't Know

>

=============== =============== =============== =============== ===============
===

>

Next, I need to calculate the percent of callers that checked each of
the following three checkbox responses (That is, the percent of
callers checking Yes, the percent of callers checking No, the
percent of callers checking Don't Know)
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>

Ok, I now have a sum of the Yes checkbox responses & now have a sum of
the No checkbox responses.

>

However, the sums are negative numbers. How can I change these sums to
positive numbers?

>

I tried changing the sums to positive numbers in a query, in a form,
and in a report...all without success!

>

Here is my SQL code for my query:

>

SELECT DISTINCTROW Sum([IBCCP Referral Query].[MinOfAgencyCont actYes])
AS [Sum Of MinOfAgencyCont actYes], Sum([IBCCP Referral Query].
[MinOfAgencyCont actNo]) AS [Sum Of MinOfAgencyCont actNo]
FROM [IBCCP Referral Query];

>

Any suggestions as how to change these sums to positive numbers?

>

Thanks!
John- Hide quoted text -

>

- Show quoted text -
>I can also use Sum(Abs([field])) but this is less accurate as you
>could have both pos (+) and Neg (-) (values).

>

If the field is true/false then you will not have an pos (+) numbers.
   Checked = True = -1
   Uncheck = False = 0- Hide quoted text -

>

- Show quoted text -- Hide quoted text -

>

- Show quoted text -- Hide quoted text -

>

- Show quoted text -

>
Yes, true. Thanks.
>
This is not the problem now.
>
My problem now is, now that I have used Sum(Abs([field]))  to convert
my neg. (-) values to pos. (+) values how can I obtain the correct
percentage.
>
For example, here is my expression from my query trying to calculate
the correct percentage, in this case, 2/2+3 = .4*100 = 40%.
>
Expr18: (((Abs(Sum([MinOfAgencyCont actYes])))/
(Abs(Sum([MinOfAgencyCont actYes])))+
(Abs(Sum([MinOfAgencyCont actNo])))))
>
Expr18 returns 400% instead of 40% due to using Sum(Abs([field])).
>
How can I correct this?
>
I am so close to completing this report.
>
Thanks,
>
John- Hide quoted text -
>
- Show quoted text -

I think I figured it out?!

Here is my new expression 18 (I divided by 10 to return the correct
percentage, in this case, 40%).

Expr18: (((Abs(Sum([MinOfAgencyCont actYes])))/
(Abs(Sum([MinOfAgencyCont actYes])))+
(Abs(Sum([MinOfAgencyCont actNo])))))/10

Thanks!!!!!!!!

John

[paii, Ron]

Re: Count Checkboxes


"zufie" <john.marruffo@ illinois.govwro te in message
news:072df733-b709-4eb0-9dd1-17271b85eda7@k3 6g2000pri.googl egroups.com...
On Sep 18, 2:59 pm, zufie <john.marru...@ illinois.govwro te:

On Sep 18, 1:45 pm, "paii, Ron" <n...@no.comwro te:
>
>
>
>
>

"zufie" <john.marru...@ illinois.govwro te in message

>

news:da036b97-8bbd-4824-a04a-f8050cae44fe@k7 g2000hsd.google groups.com...
On Sep 18, 9:54 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 18, 8:25 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 18, 8:18 am, zufie <john.marru...@ illinois.govwro te:

>

On Sep 17, 5:45 pm, "Carlos Nunes-Ueno"

<sulla...@athot maildot.com>

wrote:

>

If what you need is a total for the Yes/No fields in one row,

you

can use

the IIF function in combination with the Sum function to give a

total.

It would be something like this:

>

=Sum(IIF(YesNo1 ,1,0),IIF(YesNo 2,1,0),IIF(YesN o3,1,0), ... ,IIF
(YesNoN,1,0))

>

IIF has three arguments: 1) the expression to evaluate, 2) the

result to

return if true, and 3) the result to return if false). In this

case,

each IIF evaluates to 1 if the field is true, i.e. the box is

checked,

and to 0 if it is not. Sum adds them together to return your

result.

>

Otherwise, if what you need is a total of the Yes/Nos for the

whole

table, you can put the function above into an aggregate query

and

sum it.

The function will add the Yes/Nos in each row and the query will

sum

them

into one total.

>

If neither of those is what you're looking for, give us some

more

detail

on what you're dealing with and what you're wanting to

accomplish.

>

Hope this helps,

>

Carlos

>

zufie <john.marru...@ illinois.govwro te in

>

news:1b1b7f2f-7e9a-4ecc-9d8c-

>

7fde3aeb7...@p2 5g2000hsf.googl egroups.com:

>

I have to create a QA report regarding callers calling into a

phone

hotline.

>

The report consists of many checkboxes such as:

>

Did the IBCCP agency contact you? Yes/NO
How many days passed before you heard from the agency?

1Week/2Weeks/3

or More Weeks
Did you become an IBCCP client? Yes/NO/Dont Know

>

I thought it best to use the Caller ID to help me group

responses?

>

How best do I go about counting the respective checkboxes?

>

Thanks!

>

John- Hide quoted text -

>

- Show quoted text -

>

I need to count the total number of times each of the following

three

checkbox responses was given:
1)Yes
2)No
3)Don't Know

>

I thought it best to use the Caller ID to group the responses?

>

Next, I need to calculate the percent of callers that checked each

of

the following three checkbox responses:
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of

music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>

SORRY, I meant CHECKED:

>

I need to count the total number of times each of the following

three

checkbox responses was CHECKED:
(That is, the total number of times the Yes check box was checked,

the

total number of times the No check box was checked, the total number
of times the Don't Know check box was checked). I thought it best to
use the Caller ID to group these responses?

>

1)Yes
2)No
3)Don't Know

>

=============== =============== =============== =============== ===============

===

>

Next, I need to calculate the percent of callers that checked each

of

the following three checkbox responses (That is, the percent of
callers checking Yes, the percent of callers checking No, the
percent of callers checking Don't Know)
1)Yes
2)No
3)Don't Know

>

If that, is what you meant, then we are on the same sheet of music.

>

If not, I hope my above message made it clearer what I meant?

>

Thanks!,
John- Hide quoted text -

>

- Show quoted text -

>

Ok, I now have a sum of the Yes checkbox responses & now have a sum of
the No checkbox responses.

>

However, the sums are negative numbers. How can I change these sums to
positive numbers?

>

I tried changing the sums to positive numbers in a query, in a form,
and in a report...all without success!

>

Here is my SQL code for my query:

>

SELECT DISTINCTROW Sum([IBCCP Referral Query].[MinOfAgencyCont actYes])
AS [Sum Of MinOfAgencyCont actYes], Sum([IBCCP Referral Query].
[MinOfAgencyCont actNo]) AS [Sum Of MinOfAgencyCont actNo]
FROM [IBCCP Referral Query];

>

Any suggestions as how to change these sums to positive numbers?

>

Thanks!
John- Hide quoted text -

>

- Show quoted text -
>I can also use Sum(Abs([field])) but this is less accurate as you
>could have both pos (+) and Neg (-) (values).

>

If the field is true/false then you will not have an pos (+) numbers.
Checked = True = -1
Uncheck = False = 0- Hide quoted text -

>

- Show quoted text -- Hide quoted text -

>

- Show quoted text -- Hide quoted text -

>

- Show quoted text -

>
Yes, true. Thanks.
>
This is not the problem now.
>
My problem now is, now that I have used Sum(Abs([field])) to convert
my neg. (-) values to pos. (+) values how can I obtain the correct
percentage.
>
For example, here is my expression from my query trying to calculate
the correct percentage, in this case, 2/2+3 = .4*100 = 40%.
>
Expr18: (((Abs(Sum([MinOfAgencyCont actYes])))/
(Abs(Sum([MinOfAgencyCont actYes])))+
(Abs(Sum([MinOfAgencyCont actNo])))))
>
Expr18 returns 400% instead of 40% due to using Sum(Abs([field])).
>
How can I correct this?
>
I am so close to completing this report.
>
Thanks,
>
John- Hide quoted text -
>
- Show quoted text -

>I think I figured it out?!

>Here is my new expression 18 (I divided by 10 to return the correct
>percentage, in this case, 40%).

>Expr18: (((Abs(Sum([MinOfAgencyCont actYes])))/
>(Abs(Sum([MinOfAgencyCont actYes])))+
>(Abs(Sum([MinOfAgencyCont actNo])))))/10

>Thanks!!!!!! !!

>John

It will always be negative so you can multiply by -1 instead of using Abs
I would suggest you add a count of the key field to use in your expression.
[SumOfAgencyCont actYes] / [CountOfContactI D] * -100
Note: you can use the query field names in your expression instead of
recalculating each value.