Group totals at end of report

[clansman]

I have a report which is sorted by clients contact level ie stage 1 - no contact, stage 2 - contacted, no further action required, etc. I have created a group total that is visible at the end of each group, but would really like to show these totals at the end of the report.

Is there a quick way of doing this, or will I have to re-create these groups as individual sub-reports?

I'm pretty new to Access and am really looking for direction than an indepth, absolute answer - some of you have jobs to go to!

 

[LarryDeLaruelle]

Clansman:

Have you looked at the grouping function under Sorting and Grouping?

I think this is the tool that will give you the result you are looking for.

Hope this helps. Larry De Laruelle
ldelaruelle@familychildrenscenter.org

 

[clansman]

Larry,

Firstly, thanks for taking the time to answer my question.

I have looked at Sorting/Grouping. I currently have my Client Contact Level grouped, in descending order, with no group header, a group footer, group on each value, with a group interval of one and the whole group kept together.

The problem is that the text box that holds the group total is reset to zero (quite rightly) at the beginning of each group count.

I need to find a way to save each group total (as a seperate field) for placement at the end of the report. I don't think this is actually possible in the report's current form, but thought it might be possible if each group was formatted as a sub-report (with appopriate group total), then each sub-report pulled into a final report.

The other option seems to be to create a querie for each contact level and returning the figues recieved from each individual querie to a final report.

 

[DrSimon]

I suggest you use the Report wizard and click on the 'Summary Options' button. This will either do the job for you or show you in design form what it has done for you so you can copy to concepts.

 

[clansman]

When I use the Report wizard and look at 'Summary Options' the Client Contact Level field is not offered as an option. Presumably because it is a text field.

 

[DrSimon]

You couldn't do any sort of total on a text field anyhow. Can't you convert the text into number and try again?

 

[CosmoKramer]

I've done this before (a while ago). I think this is how I did it:

1) Clone the query (duplicate the criteria) that's the record source of your report. Set the group by field, and set your aggregate function (count, sum, etc.)

2) Create a report that has the above query as its record source. Assemble the fields you want in the detail section of this report.

3) Add the report you just created as a subreport in the report footer of your original report.

I will test this myself in the meantime.....Let me know how you make out.

 

[LarryDeLaruelle]

Clansman:

I think Cosmo is pointing you in the right direction. It sounds like you need a sub report embedded in the Report Footer section.

I use this solution frequently and usually base that subreport on a clone of the original query but use either the Totals option or set it up as a cross tab.

Hope this helps. Larry De Laruelle
ldelaruelle@familychildrenscenter.org