Query to count check boxes

T

Telobamipada

I am using MS Access 2002... I'm trying to set up a simple query that will
count how many times a check box is marked in several fields... the goal is
to create a single summary report that will show the count for each column in
my table.
 
M

[MVP] S.Clark

Queries don't know that checkboxes exist.

A query can only see data(in fields) in a table. If the field is a Yes/No
(or boolean) data type, then you can count WHERE fieldname = True.

Select FieldName, Count(Fieldname) WHERE Fieldname
 
T

Telobamipada

I'm sorry, this didn't help alot... In my field selection I chose that field,
in the totals field I chose Where, in the criteria field I entered True.
Using your example, where do you enter the "Count(Fieldname)"?

[MVP] S.Clark said:
Queries don't know that checkboxes exist.

A query can only see data(in fields) in a table. If the field is a Yes/No
(or boolean) data type, then you can count WHERE fieldname = True.

Select FieldName, Count(Fieldname) WHERE Fieldname
Click to expand...
 
T

Telobamipada

Better yet, can you just show me exactly what the SQL statement would look
like?

[MVP] S.Clark said:
Queries don't know that checkboxes exist.

A query can only see data(in fields) in a table. If the field is a Yes/No
(or boolean) data type, then you can count WHERE fieldname = True.

Select FieldName, Count(Fieldname) WHERE Fieldname
Click to expand...
 
P

PC Datasheet

This is far from simple!!

1. Add a new field named CommonField to your table and use an update query
to fill the field in all records with 1.
2. Go to your data entry form and add the following code in the form's
BeforeUpdate event:
Me!CommonField = 1
3. You need to build a separate query for each field that has checkboxes
you want to count:
a) Include CommonField and the name of the checkbox field
b} Set the criteria of the checkbox field to True
c) Click on the Sigma(looks like E) on the toolbar at the top of the
screen
d) Under the checkbox field, change Group By to Count
4. Create a new query that includes all the queries in #3 above. Join all
the queries on CommonField
5. Base your report on the query in 4. Note that all the fieldnames now
begin with "CountOf..."
 
J

John Spencer (MVP)

Field and table names are a big help. Are you using the query grid? Do you
know how to build a totals query?

You will need to use calculated fields in your query.

First, Select View:Totals from the menu

Then select your table

Then build calculated fields similar to the following.

Field: CountField1: Abs(Sum(MyCheckField1))
Total: Expression

Field: CountField2: Abs(Sum(MyCheckField2))
Total: Expression
 
T

Telobamipada

Hi John, I know for you this was probably very simple but it was a learning
experience for me! Your post was right on the mark, thanks so much.


John Spencer (MVP) said:
Field and table names are a big help. Are you using the query grid? Do you
know how to build a totals query?

You will need to use calculated fields in your query.

First, Select View:Totals from the menu

Then select your table

Then build calculated fields similar to the following.

Field: CountField1: Abs(Sum(MyCheckField1))
Total: Expression

Field: CountField2: Abs(Sum(MyCheckField2))
Total: Expression
Click to expand...
 
T

Telobamipada

John, Your solution as I said in my previous reply was superb... I have
another question. I also have fields in my table which are text fields/combo
boxes. my goal is to create a summary report which will provide totals for
many of the fields, some of which are checkboxes which are now fine. Can I
use a similar expression to also count these text fields in the same query as
I'm counting my check boxes in? Currently, In a separate query, I have to use
"grouping" before i can do counts and I really don't need to have the data
"grouped"... i just want to see the total number of fields matching specified
criteria whether or not the fields are text, check boxes, numerics... Can you
help?


John Spencer (MVP) said:
Field and table names are a big help. Are you using the query grid? Do you
know how to build a totals query?

You will need to use calculated fields in your query.

First, Select View:Totals from the menu

Then select your table

Then build calculated fields similar to the following.

Field: CountField1: Abs(Sum(MyCheckField1))
Total: Expression

Field: CountField2: Abs(Sum(MyCheckField2))
Total: Expression
Click to expand...
 
T

Telobamipada

John, I'm really sorry to do this to you but i've figured it out... Your
responses in this forum are excellent, keep up the good work.


John Spencer (MVP) said:
Field and table names are a big help. Are you using the query grid? Do you
know how to build a totals query?

You will need to use calculated fields in your query.

First, Select View:Totals from the menu

Then select your table

Then build calculated fields similar to the following.

Field: CountField1: Abs(Sum(MyCheckField1))
Total: Expression

Field: CountField2: Abs(Sum(MyCheckField2))
Total: Expression
Click to expand...
 
J

John Spencer (MVP)

Not really, the problem is that you are going from a two-value field to a
multi-value field. You can perhaps works this out using something like

Field: CountRed: Abs(Sum([ColorField]="Red"))
Field: CountBlue: Abs(Sum([ColorField]="Blue"))
Field: CountOrange: Abs(Sum([ColorField]="Orange"))

Field: CountSize10: Abs(Sum([Size]<=10))
Field: CountSize20: Abs(Sum([Size]>10 AND [Size]<=20))
....

The problem with this is that you are (1) limited to a maximum of 255 columns
and (2) have to build one calculated column for each possible returned value or
set of values for each field.
John, Your solution as I said in my previous reply was superb... I have
another question. I also have fields in my table which are text fields/combo
boxes. my goal is to create a summary report which will provide totals for
many of the fields, some of which are checkboxes which are now fine. Can I
use a similar expression to also count these text fields in the same query as
I'm counting my check boxes in? Currently, In a separate query, I have to use
"grouping" before i can do counts and I really don't need to have the data
"grouped"... i just want to see the total number of fields matching specified
criteria whether or not the fields are text, check boxes, numerics... Can you
help?
Click to expand...
 
Top