Show group totals in Report Header? 1

[huskerdon]

Hi,

I did a search, but didn't find anything similar to this - so here goes:

What I would like to do if possible, is show totals from a group footer in the Report Header. I've set up just ONE group using the sorting and grouping section. It is grouped on the Due_Date field. I used a text box named txtRecordCount, with the ControlSource set to "=1" with the running sum set to "Over Group". This shows the count of each record in a group. Then in the group footer, I have a text box named txtGroupTotal, with the ControlSource set to "=[txtRecordCount], and this is showing the total # of records in each group.

Both of these text boxes are working fine, and display the proper values. My first question is this - is there a way to get the # of different groups there are, ie the # of different Due_Dates? I suppose I could get it by getting the Count of the # of distinct Due_Dates - but I was wondering if there is already a function that returns this group count.

The second question, (once I know the # of distinct groups) is whether it's possible to show these counts in the Report Header? For example, I would like to show:

10 Due by 9-25-04
7 Due by 10-1-04
12 Due by 10-8-04

It would have to loop through the # of groups to determine the correct # of labels or text boxes to put in the Report Header.

If anyone has any suggestions, I would appreciate it. Thanks!

 

[misscrf]

I dont know if this is what you are looking for, but I would open the field list, and insert that field into its header, right click and build. then I would put the sum function on it. That should do what you want, but I may not be fully getting this right.


misscrf

Management is doing things right, leadership is doing the right things

 

[huskerdon]

misscrf,

Thanks for replying. What you suggested won't work, because it will just show the Due_Date value for the first record, and I don't want to sum the date field.

For example, say I have a table with 29 records. 10 have a Due_Date of 9-25-04, 7 have a Due_Date of 10-1-04, and 12 have a Due_Date of 10-8-04. (The number of different due dates can vary - sometimes there may be just 1 or there may be more than 3.) I would like to be able to show a list of the due dates at the beginning, in the Report Header.

I did figure out a way to do it (sort of!), but it's probably not the best solution. Here's what I did:

1) I created a query called "qryDueDateCount", which lists the DISTINCT due dates. In this case it would just have 3 records: 9-25-04, 10-1-04, and 10-8-04.

2) In the ReportHeader_Print procedure, I set a recordset that uses this query as the source. Then I loop through the records and build a global string that contains the Due_Date and corresponding # of records for that date. I separate each line with a vbCrLF.

3) After looping through the recordset, I then set a text box in the Report Header = this global string. It works great when I have the report in Design view, and then preview it. But when I open it from a form using the OpenReport method, this text box does NOT display !!

I can even display a msgbox showing the value of the string - and it is correct - but the text box does not print, when opening the report via code, or even by double-clicking it from the DB Window. The only time it prints is if I'm in design view first.

Here's where it gets even stranger. If I put the recordset looping code in the ReportHeader_FORMAT procedure, the text box appears, but everything is listed TWICE.

If anybody can help explain what is going on I would really appreciate it. Why does the ReportHeader_Format event happen twice ???

 

[misscrf]

maybe when you open it from the form, you need to reset the text box = global string again. Can you do that from the click or on open code?


misscrf

Management is doing things right, leadership is doing the right things

 

[CosmoKramer]

huskerdon,

Why don't you try a subreport approach?? Create a new query that uses your existing query as its source and will count the occurences by date. Something like:

SELECT YourReportQuery.Due_Date, Count(YourReportQuery.Due_Date) AS CountOfDue_Date
FROM YourReportQuery
GROUP BY YourReportQuery.Due_Date;

Create a simple report using this new query as its record source and insert this new report as a subreport into your main report's page header....

Hoc nomen meum verum non est.

 

[misscrf]

cosmo, what does "Hoc nomen meum verum non est" mean?

misscrf

Management is doing things right, leadership is doing the right things

 

[CosmoKramer]

This isn’t my real name


Hoc nomen meum verum non est.

 

[huskerdon]

CosmoKramer,

Thanks for the suggestion. That worked great! Sometimes the solution is easier than we think.

I modified the "qryDueDateCount" query mentioned above to this:

SELECT DISTINCT tblRetailer_Info.Due_Date, Count(tblRetailer_Info.Due_Date) AS CountOfDue_Date
FROM tblRetailer_Info
GROUP BY tblRetailer_Info.Due_Date;

"tblRetailer_Info" is the name of the table with the source records. This is a good solution, and doesn't require any code on the report.

Thanks CosmoKramer!