subtotals and groups

[chrismontora]

Hi.
I have a report with 3 levels of grouping:G1,G2,G3 and details. It will look like this:
G1
G2
G3
details (value)
G3
details (value)
G2
G3
details (value)
................

1. How can I calculate subtotals for each group?
2. Can I place subtotals in group header, not in footer?

Update: 1. How can I calculate for G1,G2,G3 subtotals of values from another table? Can I create custom fields to calculate this: "SELECT SUM(Dvalue) FROM AnotherTable WHERE G1 AND G2 AND G3"?

Thanks.

 

[GabrielR]

Sometimes when I face this type of problem what I do is that I define a "Dummy" Field in the query that is the record source of the report, that is, creating an Empty field in the query (see Example attached) and the SQL counterpart

SELECT tblSample.Id, tblSample.First, tblSample.Last, "" AS Dummy
FROM tblSample;

and create a group for that Dummy field place that in the report after the group you want a total, set up the Count([YourField]) or Sum([YourField]) there, and that will actually do the trick.

 

[Pat Hartman]

1. How can I calculate subtotals for each group?

Add a footer for each group.
Add a control to sum the column. Its ControlSource should be:
=Sum(yourfield)

2. Can I place subtotals in group header, not in footer?

It seems to work but it forces Access to process your query twice so I hope your recordset isn't large.