Count Check Boxes on MS Access Report 1

[FrChopin]

Hello,

I am new to MS Access (using Access 2000) and I have
created a form with 30 or so check boxes. The report
needs to show the number of males/females for each
check box. I can get the grand total of all the checked
boxes for males/females but I can't
figure out how to do the count for each check box grouped
by male/female. From searching for an answer on the Web it seems it would help if I knew SQL, but I don't.
I know how to do this in EXCEL using the countif
function, but am not sure if EXCEl functions will work in Access. Before I try I thought I would ask those more
familiar with ACCESS !

Can you point me in the right direction.

Any help is greatly appreciated

FrChopin

 

[TonyJollans]

Hi FrChopin,

Not sure I quite understand what you have, but assuming you have a Yes/No field in a Table and want to count the number of records which have it set to Yes, then SQL like this will do it ..

SELECT COUNT(*) FROM Table WHERE YesNoField=Yes;

In a Form or a Report, you can use the DCount Function to provide the same information.

Please post back with a few more specific details if this is not enough to put you on the right path.



Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[FrChopin]

Hi Tony,

I tried your suggestion and this is what appeared on the
report in the "sex" group footer:

#name2

This is the syntax I used based on your suggestion. I
probably have it wrong: (I entered this in a text
box in the "sex" group footer of the report)

=(SELECT COUNT(*) FROM [Master Ancestry Pivot - NON VOCA - Monthly] WHERE [Other Crimes]="YES&quot

 

[TonyJollans]

Hi FrChopin,

In a Textbox in a Form try using the DCount Function ..

DCount("[Other Crimes]","[Master Ancestry Pivot - NON VOCA - Monthly]","[Other Crimes]=Yes&quot


Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.

 

[FrChopin]

Hi Tony,

I discovered that if I place the following code
in a text box in the header of my group on the report
I get a count of the checked "yes" boxes:
=Sum(Abs([Yes/No Field Name]))


Trouble is I have to do this each time for each column
(Yes/No Field Name) on the report and have to mess with
alignment of the text boxes. Is there a quicker
way to do this?

Again...any help is greatly appreciated

FrChopin .... should be writing tunes....

 

[TonyJollans]

Hi FrChopin,

Yes, that should work.

For a one-off there's no real way I know of simplifying it - obviously if you had to do hundreds it ought to be possible to code something. If I have an idle moment, I'll think about it but to consider all the possibilities will probably take longer than it will take to do the whole process manually.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.