Counting...another spin on it

L

Laine

I have received great help here, have searched, but cannot locate answer to
question.

I have a table with 170 records, each record has 10 yes/no fields in it.

I need to count the number of times "yes" occurs in the entire table, and
the number of times "no" occurs in the entire table - not just on each record.

Any suggestions?

Thanks in advance.
 
J

JB

One thought would be to add NumYes and NumNo fields to each record that are
edited each time the record is changed and then run a Sum(NumYes) ,
Sum(NumNo) query on the table.
 
J

JB

In SQL Server, you could do this with a trigger but I don't know if there is
something similar in Jet
 
D

Duane Hookom

Normalize your table with a union query named "quniMyUnionQuery" and SQL
like:

SELECT 1 as AYes
FROM tbl10YesNos
WHERE YesNo1 = True
UNION ALL
SELECT 1
FROM tbl10YesNos
WHERE YesNo2 = True
UNION ALL
SELECT 1
FROM tbl10YesNos
WHERE YesNo3 = True
UNION ALL
...etc...
SELECT 1
FROM tbl10YesNos
WHERE YesNo10 = True;

Then use
SELECT Sum(AYes) as CountOfAllYes
FROM quniMyUnionQuery;
 
D

Dirk Goldgar

Laine said:
I have received great help here, have searched, but cannot locate
answer to question.

I have a table with 170 records, each record has 10 yes/no fields in
it.

I need to count the number of times "yes" occurs in the entire table,
and
the number of times "no" occurs in the entire table - not just on
each record.

Any suggestions?

Thanks in advance.
Click to expand...

*IF* this is a Jet table (not linked to an ODBC data source), and if
you're willing to rely on the internal storage format that Jet uses for
boolean (yes/no) fields -- not necessarily good practice, but maybe
acceptable if you're sure your database won't be upsized -- then you
could write something like this:

SELECT
Abs(Sum(YN1+YN2+YN3+YN4+YN5+
YN6+YN7+YN8+YN9+YN10))
As Yes_Count,
((10 * Count(*)) - Yes_Count) As No_Count
FROM MyTable;
 
L

Laine

Perfect Dirk! Thanks so much.

Laine

Dirk Goldgar said:
*IF* this is a Jet table (not linked to an ODBC data source), and if
you're willing to rely on the internal storage format that Jet uses for
boolean (yes/no) fields -- not necessarily good practice, but maybe
acceptable if you're sure your database won't be upsized -- then you
could write something like this:

SELECT
Abs(Sum(YN1+YN2+YN3+YN4+YN5+
YN6+YN7+YN8+YN9+YN10))
As Yes_Count,
((10 * Count(*)) - Yes_Count) As No_Count
FROM MyTable;

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Top