How do I count checks in checkboxes? 1

[Shinken]

I have a table with several checkboxes, to denote tests passed. A checked box denotes a passed test, an unchecked box denotes a failed test.

For example:

[ ] part1
[ ] part2
[ ] part3
[ ] part4
[ ] part5

Another table lists the participant's characteristics, such as Date of Birth, Gender, etc.

A simple query (qryTestResults) combines both tables, which are related by an ID number.

I wish to develop a report that shows the total number of each passed test, separated by Gender and age above & below 25 yrs.

I would like the results to look something like this (note that I'm using periods only to set the row heading spacing in this message)

.........................Part1....Part2....Part3....Part....Part5
Male <25
Male >= 25
Female <25
Female >= 25

I'm having two problems.

1. What expression do I use to count the total number of checked checkboxes in each category/column (part1, part2, etc)?

2. Can I do this in a single crosstab query, or should I use 2 (or more) crosstab queries, one for Gender=Male and one for Gender=Female?

I'm assuming that whatever query(s) I use, should be based on the simple query (qryTestResults), but I may be wrong, and perhaps should take the data directly from the 2 tables.

I somehow need to get the data into a single crosstab query so that I can create a chart of the results.

All suggestions are appreciated.

Thanks,

Shin

 

[Haitham]

At This Case, Can You Send Me Your DB And Let Me Look Around To Do What You Need, Because I Got Confuse Of That Puzzel CheckBoxes............))

Best Regards
Haitham

 

[Haitham]

At This Case, Can You Send Me Your DB And Let Me Look Around To Do What You Need, Because I Got Confuse Of That Puzzel CheckBoxes............))

Best Regards
Haitham
Demashqe@yahoo.com

 

[PaulBricker]

This select statement should get you started.
SELECT tblTestScores.Gender, IIf([Age]>=25,&quot;Over25&quot;,&quot;Under25&quot AS GroupAge, Sum(IIf([Part1]=-1,1,0)) AS Part1Total, Sum(IIf([Part2]=-1,1,0)) AS Part2Total
FROM tblTestScores
GROUP BY tblTestScores.Gender, IIf([Age]>=&quot;25&quot;,&quot;Over25&quot;,&quot;Under25&quot;
This returns Sums for Part's 1 and 2 grouped by gender and age. You may need to do some adjusting based on how your information is set up. It sounds like you will have to calculate Age based on DOB in a seperate query. If you want to post a little more info, it would help.

From here, you would probably put this in a single crosstab query to get the information in the form you need for a graph. Change table and field names where appropriate.

Paul

 

[raskew]

Paul & Shin-
Go here:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=36495

Bob