subtotals in reports

[spartansFC]

Hi

I know how to create subtotals in reports and i know how to create count/sum queries when it's only dealing with one criteria, but i've got 2 types of criteria to deal with now.

I've attached an example of one of my queries. The example shows at the minute that there are 3 types of child at risk:

Involved, Considered and Neither and these options can have 2 group types, Group Parenting or One to One.

My problem is, how do i set up a report or query so that it subtotals the Child at risk including the 2 group types.

I've got round this by creating 3 seperate queries and putting in the criteria 1 of the 3 different "ChildatRisk", and then i create a seperate subreport to put in my main report.

The problem with creating seperate queries is, what happens if there is another "ChildatRisk" category created, i then have to create another seperate query and subreport.

I'm not sure if a crosstab query would work better or if i need a bit of coding to subtotal.

In my attached example, forgetting about the blank entries, the totals would be

Involved = 26
Considered = 176
Neither = 62

thanks for all your help in all my previous questions

Mikie

 

[dhookom]

Isn't Involved = 28 or am I missing something?

If all you want is to display the three text values and the sum of the values from you query, you could create a report/subreport based on

SELECT Parent_ChildatRisk, Sum([CountOfParentChild_ID]) as TotalCount
FROM [one of my queries]
WHERE Parent_ChildatRisk is not null
GROUP BY Parent_ChildatRisk;

Duane
Hook'D on Access
MS Access MVP