Counting checkbox No's

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

Guest

I know there is an easy way to count the yes's in a checkbox using a query
but how about counting the no's?

Or

Is there a way to take the total amount of checkboxes and subtract the
yes's? (this would = the no's)

And

How would I get the % of yes's from the total?

EX:

All Repairs = 118 (Total)
Repairs Done = 4 (Count of Yes's)
Repairs Not Done = 114 (Count of No's OR (All Repairs - Repairs Done))
% of Repairs Complete = 3.4% (% of Yes's from All Repairs)

Thanks!
 
Thanks for the responce but I know how to count yes.
I need to know if there is a way to do all the stuff I asked =)

KARL DEWEY said:
A checkbox yes is stored as a -1. Use this to count yes --
Sum(Abs([YourCheckBox])
Click to expand...
 
Hi, Joel.

You can do this easily in a totals query. For a table called Checkboxes,
the total number of No responses in a field called Check1 is calculated by
the SQL query:

SELECT Count(Checkboxes.Check1) - Abs(Sum(Checkboxes.Check1)) AS NumberofNos
FROM Checkboxes;

To calculate the fraction of Yes responses from the total number of records,
the SQL is:

SELECT Abs(Sum([Checkboxes].[Check1]))/Count([Check1]) AS Check1Fraction
FROM Checkboxes;

Hope that helps.
Sprinks
 
Thanks a bunch Sprinks.
That did the trick.

Sprinks said:
Hi, Joel.

You can do this easily in a totals query. For a table called Checkboxes,
the total number of No responses in a field called Check1 is calculated by
the SQL query:

SELECT Count(Checkboxes.Check1) - Abs(Sum(Checkboxes.Check1)) AS NumberofNos
FROM Checkboxes;

To calculate the fraction of Yes responses from the total number of records,
the SQL is:

SELECT Abs(Sum([Checkboxes].[Check1]))/Count([Check1]) AS Check1Fraction
FROM Checkboxes;

Hope that helps.
Sprinks

Joel said:
I know there is an easy way to count the yes's in a checkbox using a query
but how about counting the no's?

Or

Is there a way to take the total amount of checkboxes and subtract the
yes's? (this would = the no's)

And

How would I get the % of yes's from the total?

EX:

All Repairs = 118 (Total)
Repairs Done = 4 (Count of Yes's)
Repairs Not Done = 114 (Count of No's OR (All Repairs - Repairs Done))
% of Repairs Complete = 3.4% (% of Yes's from All Repairs)

Thanks!
Click to expand...
Click to expand...
 
Relying on the implementation is generally not considered good programming
practice. Its better to work with Boolean TRUE and FALSE values rather than
their implementation in Access as -1 and 0. To count TRUE values you can use:

SUM(IIF(MyBooleanColumn,1,0))

Similarly to count FALSE values:

SUM(IIF(MyBooleanColumn,0,1))

You can extend this to other data types of course, e.g. to count all
instances of 'Widget' in a Product column:

SUM(IIF(Product = 'Widget',1,0))

In each of the above the return value of the IIF function is determined by
the evaluation of the first argument as TRUE or FALSE, returning 1 or 0.
These return values are then summed, so the implementation is irrelevant,
which is how it should be.

Joel said:
Thanks a bunch Sprinks.
That did the trick.

Sprinks said:
Hi, Joel.

You can do this easily in a totals query. For a table called Checkboxes,
the total number of No responses in a field called Check1 is calculated by
the SQL query:

SELECT Count(Checkboxes.Check1) - Abs(Sum(Checkboxes.Check1)) AS NumberofNos
FROM Checkboxes;

To calculate the fraction of Yes responses from the total number of records,
the SQL is:

SELECT Abs(Sum([Checkboxes].[Check1]))/Count([Check1]) AS Check1Fraction
FROM Checkboxes;

Hope that helps.
Sprinks

Joel said:
I know there is an easy way to count the yes's in a checkbox using a query
but how about counting the no's?

Or

Is there a way to take the total amount of checkboxes and subtract the
yes's? (this would = the no's)

And

How would I get the % of yes's from the total?

EX:

All Repairs = 118 (Total)
Repairs Done = 4 (Count of Yes's)
Repairs Not Done = 114 (Count of No's OR (All Repairs - Repairs Done))
% of Repairs Complete = 3.4% (% of Yes's from All Repairs)

Thanks!
Click to expand...
Click to expand...
Click to expand...
 
Back
Top