Summarising Data

[hect1c]

Hi,



This questions relates to counting check boxes stored in a table and presenting those totals in a report.

I would like my report to look like this:

	Total check boxes TRUE
Category 1	5
Category 2	4
Category 3	2
Category 4	3
Category 5	1

Each record in the main table has a value for at least one of these 5 categories.

Is it possible to setup a count in the report design or do I need to create a query that would summarise the totals first? I basically want Access to count all records that are TRUE.

Does this sound like check boxes have been setup correctly? What is the best way to store the values of check boxes?

Thank you.

Cheers
Chris

[June7]

This is one yes/no field?

Expression in textbox of Category group footer:

=Abs(Sum([checkbox field]))

[hect1c]

Hi June 7,

Thank you for your reply. The user will select 1 or more the activity topics by ticking a check box on a form. I am trying to figure out the best way to count how many records have primary care true, how many records have actue care true etc so I can show this in a report. Should this be done by creating a query and using that query in the report design or creating a query within the report desing?

The data is stored in the database like this.
The values in bold are the numbers I would like to obtain by counting.

Activity_Topic_PrimaryCare	Activity_Topic_AcuteCare	Activity_Topic_EOL_Palliative	Activity_Topic_EnviroDesign	Activity_Topic_MedMgt
TRUE	FALSE	FALSE	FALSE	FALSE
FALSE	FALSE	TRUE	FALSE	FALSE
FALSE	TRUE	TRUE	FALSE	FALSE
FALSE	TRUE	TRUE	FALSE	FALSE
FALSE	TRUE	FALSE	TRUE	FALSE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	TRUE	FALSE	TRUE	TRUE
FALSE	FALSE	FALSE	TRUE	FALSE
FALSE	FALSE	FALSE	FALSE	FALSE
1	5	3	4	2
Using this data I would like to create this type of report in the report design.
Activity Topic	No of Activties
PrimaryCare	1
AcuteCare	5
Palliative	3
EnviroDesign	4
MedMgt	2

[June7]

Your data structure is not normalized. Therefore, producing a report with that output is more difficult. Will need a UNION query to manipulate the data to a normalized structure then another query to do aggregate calcs. Or use the UNION as report RecordSource and do summary calcs in report group footer. A report will allow display of detail info as well as summary calcs.

Actually, the UNION and aggregate calcs can be done in one SQL statement.

There is no wizard or builder for UNION. Must type into SQL View of query designer.