Counting checkbox No's

[Joel]

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!

 

[KARL DEWEY]

A checkbox yes is stored as a -1. Use this to count yes --
Sum(Abs([YourCheckBox])

 

[Joel]

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

A checkbox yes is stored as a -1. Use this to count yes --
Sum(Abs([YourCheckBox])

 

[Sprinks]

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]

Thanks a bunch Sprinks.
That did the trick.

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

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!

 

[Ken Sheridan]

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.

Thanks a bunch Sprinks.
That did the trick.

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

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!