Trouble summing yes/no checkbox fields in query

[katy.wortman]

In my database, I have checkboxes for 6 different industries. I would
like to create a report that shows the total number of records where
the box is checked for each of the six industries. To do this, I
figured I would first need to create a query.

I went through the query design wizard and created a query that would
sum each of the 6 variables and the show the total count of records in
the table.

When I run the query, I get yes/no answers for each field, not numbers
(what I would like to see).

One thing that seems weird to me is that when I click in the query
results field for each individual variable, the correct number
appears. However, as soon as I click outside of the field, it returns
to yes/no rather than a number. When I create a report using data
from this query, it also returns yes/no as the sums rather than
numbers.

My SQL code:
SELECT DISTINCTROW ABS(Sum([Corporate Practice 012207].[Food and
Beverage])) AS [Sum Of Food and Beverage], ABS(Sum([Corporate Practice
012207].Automobile)) AS [Sum Of Automobile], ABS(Sum([Corporate
Practice 012207].Pharmaceutical)) AS [Sum Of Pharmaceutical],
ABS(Sum([Corporate Practice 012207].Guns)) AS [Sum Of Guns],
ABS(Sum([Corporate Practice 012207].Alcohol)) AS [Sum Of Alcohol],
ABS(Sum([Corporate Practice 012207].Tobacco)) AS [Sum Of Tobacco],
Count(*) AS [Count Of Corporate Practice 012207]
FROM [Corporate Practice 012207];



Any help or ideas would be great!

 

[Michel Walsh]

It is easier, in that case, to drag the six fields in the grid and add a
criteria telling they must all be true (in the criteria line of the grid).
Drag, in the grid, the other required fields, that would be ... done.



Hoping it may help,
Vanderghast, Access MVP

 

[katy.wortman]

It is easier, in that case, to drag the six fields in the grid and add a
criteria telling they must all be true (in the criteria line of the grid).
Drag, in the grid, the other required fields, that would be ... done.

Hoping it may help,
Vanderghast, Access MVP

I'm sorry, I don't think I understand what you mean by dragging fields
in the grid...

 

[Michel Walsh]

You are using the graphical editor for a new query? If so, have the table in
the top half, then, drag each of the 6 "check" fields, in the grid, one at
a time, in the lower half of the editor. In the grid, of the editor, spot
the line criteria, add

<> false

for these columns that are your check fields you just dragged.

Drag the other fields, the ones you want to see, in the grid. And that's
all. Take a look in the data view.


Hoping it may help,
Vanderghast, Access MVP

 

[katy.wortman]

You are using the graphical editor for a new query? If so, have the table in
the top half, then, drag each of the 6 "check" fields, in the grid, one at
a time, in the lower half of the editor. In the grid, of the editor, spot
the line criteria, add

<> false

for these columns that are your check fields you just dragged.

Drag the other fields, the ones you want to see, in the grid. And that's
all. Take a look in the data view.

Hoping it may help,
Vanderghast, Access MVP

Thanks Michel - but when I do this, I see a check boxes where I want
to see numbers. I would like the query to return the number of
records where each particular field is checked. Does this make sense?

Thanks again -

Katy

 

[Michel Walsh]

Ha, I thought you wished to see the records where all the 6 fields were
checked.

SELECT ABS(SUM(ckField1)), ABS(SUM(ckField2)), ABS(SUM(ckField3)),
ABS(SUM(ckField4)), ABS(SUM(ckField5)), ABS(SUM(ckField6))
FROM myTable


will do what you want. Replace the ckFieldx fields with yours, same for
myTable. It is based on the fact the true (or yes) = -1 and false (or no) =
0. So, summing the values is the same as counting the true... ABS, the
absolute value, returns a positive value.


Hoping it may help,
Vanderghast, Access MVP

 

[katy.wortman]

Ha, I thought you wished to see the records where all the 6 fields were
checked.

SELECT ABS(SUM(ckField1)), ABS(SUM(ckField2)), ABS(SUM(ckField3)),
ABS(SUM(ckField4)), ABS(SUM(ckField5)), ABS(SUM(ckField6))
FROM myTable

will do what you want. Replace the ckFieldx fields with yours, same for
myTable. It is based on the fact the true (or yes) = -1 and false (or no) =
0. So, summing the values is the same as counting the true... ABS, the
absolute value, returns a positive value.

Hoping it may help,
Vanderghast, Access MVP

Thanks again for your reply. However, the sql code you post above is
essentially what I had in the beginning, and I am still getting
answers of "yes" and no" in the query, not the numbers I am looking
for. I will copy and paste the sql below. Any ideas? This seems
really bizarre to me.

SELECT ABS(Sum(Food and Beverage)) AS [Sum Of Food and Beverage],
ABS(Sum(Automobile)) AS [Sum Of Automobile], ABS(Sum(Corporate
Practice 012207)) AS [Sum Of Pharmaceutical], ABS(Sum(Guns)) AS [Sum
Of Guns], ABS(Sum(Alcohol)) AS [Sum Of Alcohol], ABS(Sum(Tobacco)) AS
[Sum Of Tobacco], Count(*) AS [Count Of Corporate Practice 012207]
FROM [Corporate Practice 012207];

 

[John Spencer]

In design view of your query, make sure that the fields are not formatted as
yes/no fields using the format properties

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ha, I thought you wished to see the records where all the 6 fields were
checked.

SELECT ABS(SUM(ckField1)), ABS(SUM(ckField2)), ABS(SUM(ckField3)),
ABS(SUM(ckField4)), ABS(SUM(ckField5)),
ABS(SUM(ckField6))
FROM myTable

will do what you want. Replace the ckFieldx fields with yours, same for
myTable. It is based on the fact the true (or yes) = -1 and false (or
no) =
0. So, summing the values is the same as counting the true... ABS, the
absolute value, returns a positive value.

Hoping it may help,
Vanderghast, Access MVP

Thanks again for your reply. However, the sql code you post above is
essentially what I had in the beginning, and I am still getting
answers of "yes" and no" in the query, not the numbers I am looking
for. I will copy and paste the sql below. Any ideas? This seems
really bizarre to me.

SELECT ABS(Sum(Food and Beverage)) AS [Sum Of Food and Beverage],
ABS(Sum(Automobile)) AS [Sum Of Automobile], ABS(Sum(Corporate
Practice 012207)) AS [Sum Of Pharmaceutical], ABS(Sum(Guns)) AS [Sum
Of Guns], ABS(Sum(Alcohol)) AS [Sum Of Alcohol], ABS(Sum(Tobacco)) AS
[Sum Of Tobacco], Count(*) AS [Count Of Corporate Practice 012207]
FROM [Corporate Practice 012207];

 

[katy.wortman]

In design view of your query, make sure that the fields are not formatted as
yes/no fields using the format properties

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County

Thank you so much! I'm relatively new to queries and had no idea it
shouldn't be formatted as yes/no - problem solved!

 

[John Spencer]

When you format a number as Yes/No it will display No if the number is zero
and Yes if the number is Not zero.

Setting formats is queries using the field's format property is usually a
waste of time and effort. (In my opinion)
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..