summary control on report

[AudreyCole]

Hi. I want to count up the number of GROUPS on a report, not the number of records. I thought I could do this by using a running sum on a control in the group, but, alas, that doesn't work. I have clients with projects; sometimes a client will have multiple projects. The report is grouped by client, with a header for client info, and the project info is in the detail band. Any control I try to use with running sum, such as client ID in the group header, always counts the number of records (number of projects) instead of the number of discrete clients.

There must be something out there that does this. Any ideas?

Thanks,

Audrey Cole

 

[Tranman]

Audrey
You might try incrementing a global counter in the Format or Print Event of the Group Header (or Footer).
Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant."
Mark Twain

 

[AudreyCole]

Tranman: Thanks for the idea. I'll give it a try.

Audrey

 

[AudreyCole]

Hi again. I tried creating a global counter in the OnOpen of the report, an integer that I set to zero, and incrementing it in the OnFormat of the group header. I'm having trouble with 1. making it global so the header event knows about the counter and 2. putting it on a report control. Do I need a control in the header to keep track of the number? I think not. In the control which will show the final value in the report footer, is the control source set to the global variable name? Is it [intCounter] or =[intCounter]?

I feel like I'm almost there, but not quite. Any help is much appreciated.

Thanks,

Audrey Cole

 

[Tranman]

Hi Audrey,
Sorry, I might have been a little more specific about how to accomplish what you're wanting to do.

In the General Declarations section of your Report Module (at the very top), where it says:

Option Compare Database
Option Explicit

Add this:
Public intCustCtr As Integer

Then in the Report Open Event, say:
intCustCtr = 0

Then in the Group Header Print Event, say:
intCustCtr = intCustCtr + 1

Then, in the Report Footer Format Event, say:
<myControlName> = intCustCtr
(where <myControlName> is an **unbound** control)

Write back if you continue to have problems.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[AudreyCole]

Hi, Tranman: Thanks so much for your reply. I implemented your ideas, and get interesting but not accurate results.

I put the incrementing code in the OnFormat of the group header (I'll describe putting it in OnPrint below). Whether I use OnPrint or OnFormat in the report footer, and whether I print or print preview the report, I have a messagebox that tells me that when the report comes up showing page 1 on the screen or to the printer, I get a correct count of 38. When it prints page two, the message box now tells me the count is 76. If I print directly, that's the [incorrect] number I get as a result. If I preview, then page 3 (which has the final 10 records on it), gives me a total count of 86, which is also incorrect.

There's obviously something going on in the format event that I'm not understanding.

When I put the incrementing code in the OnPrint event of the group header, I get a count of 21, or 37 depending on if I preview the pages or not. Sigh...

I would like to be able to preview if the client wants to, but I obviously need a correct count in any case.

Any other ideas or hints for me?

Thanks again so very much for your help,

Audrey

 

[Tranman]

Audrey,
Are you remembering to reset the count to zero each time you open the report? And being sure not to increment the counter in both the Format and Print events?

Then in the Report Open Event, say:
intCustCtr = 0

I have replicated what I suggested in a dummy mdb with the counter being incremented in the Print Event, and it seems to work fine, either in Preview, or when printing.

Format Event fired too many times, so that didn't work (similar to your results).

Is your messagebox in the REPORT footer? If not, you will be displaying intermediate results.

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[AudreyCole]

Hi again, Tranman: I am indeed following your instructions. When I create a query that has only enough records to fit on a single page, it all works fine. If I have more than a single page worth, that's where the variability comes into play.

I created a group footer to start a new page after each record, which allows me to perhaps understand better how the formatting is figuring in here. I have a count that should be 29. When I print preview, if I jump from the first page, which has a single record on it, to the last page, which has only the totals, the count gives me 1. When I flip through 4 (or 12 or some number) of the pages in print preview, that number is what the count gives me.

So the "OnPrint" only hits AND GETS COUNTED if you preview the records on that page. I printed directly, and the count seems to work fine then. Similarly, when I create a page break before the report footer, so that I can print preview page 1 and then jump to the last summary page, I reliably get the count to give me whatever the number of records in the first page is. If I preview each page, I get the correct number.

I'll have to check to see if I get the same results with the incrementing code in the OnFormat event. So it looks like I'm OK if I print directly, but can't rely on the number if I preview anything over a single page.

Hmmm...Closer, but it's still not a great solution.

Thanks again for your help. I'd be happy to hear if you have any other comment, or if you can generate results different from these.

Audrey

 

[jsteph]

Check the FormatCount in the Format events, only run the code when FormatCount = 1, this may help. Also, look at the OnRetreat as well.
--Jim

 

[AudreyCole]

Jim: Thanks for your response. Good idea about the format/print count. That will perhaps keep the number from being LARGER than it should be, which I'd seen before.

Unfortunately, it doesn't help the situation in which if I have more than 2 pages in print preview and only look at a subset of those pages - say I have a 7 page report and only look at 2 pages in print preview - it only counts the records for the pages I've actually looked at (formatted) in print preview, so it tells me the count is 22 instead of 79.

Thanks again,

Audrey

 

[Tranman]

Audrey,
Here's another way...
Write a query that counts the number of groups in your report. Write a third query that becomes the source for your report. The third query has the original query plus the second query as its data source--without a join between the two (so you get a cartesian product--the only row returned in the second query--the group count) appears on EVERY row of the third query. Then just put a control in the report footer that has the group count as its source.

No more preview problems--no more format problems--no more print event problems...no problems. I wrote one and it works great (not elegant, but it works great).

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[AudreyCole]

Tranman: Thanks for the idea. That's basically what I've done to get the reports working, but it bugs me to have to manage another 148 queries for the reports that need stuff like this counted. I kept thinking: There HAS to be a better way! Perhaps not.

Thanks again. I appreciate your help immensely!

Audrey

 

[Tranman]

Audrey,
148 queries...[Tranman closes his eyes and imagines your database window]

It seems like you could do it with 1 query and plug a where clause into it on the fly.

I wrote a reporting app for a customer last fall, and tricked it out so the reports were always launched from a form where they would pick their criteria from a bunch of dropdown lists, then punch a command button to launch the chosen report. All reports were based upon the same query, and when the button was clicked, it replaced the SQL of that query with an appropriate statement, then opened the report.

I'd do almost anything before I wrote 148 queries

Tranman

"Adam was not alone in the Garden of Eden, however,...much is due to Eve, the first woman, and Satan, the first consultant." Mark Twain

 

[jsteph]

Well, a brute-force method would be to create a subquery:

SELECT 1 as x
FROM [table_or_query_That_is_Report_source]
WHERE [SAME CRITERIA AS REPORT USES]
GROUP BY [SomeField That Is a Report Group]

Name it "somesubquery"

Then your report's group count for this group is
=DCOUNT("x","somesubquery")
You need to be sure how you're filtering/criteriaizing the report, and match that to the first Querie's WHERE clause.

The dcount can be the controlsource of a control on the Report header or used anywhere you like.
--Jim

 

[AudreyCole]

Jim & Tranman: Thanks again for your feedback. In fact, I am typically using a subquery with a dcount. Occasionally, I'll use a subreport if there are several items to count.

I normally try to avoid domain functions because of speed, and try to avoid managing extra items, such as queries or subreports, if I can find a smarter way to get what I want. Again, "There's gotta be a better way" still pesters me! Perhaps some compromise of writing a few queries and coding some sql into them, etc.

Thanks again for all your efforts.

Audrey

 

[jsteph]

Hi Audrey,
The subquery method is not costing you much more than any other method might. As far as dcount(), I used dcount() as an easy example, you could use another query with the subquery as source, or some other method, but early on I had a similar aversion to the domain functions, and I did some perf tests using queries, recordsets, etc, using all sorts of optimized settings (table-type with seek, etc, snapshot, etc) and the domain functions were faster them the majority of methods, and slower than only the most highly optimized table-type recordsets. Also they're a hell of a lot less coding and you can't use table-type on anything but a local jet table, unless you do even more coding and open the remote db, etc.

Anyway, as far as using a subquery or whatever--think about it--if the report is to know the grouping levels and figure out the number of them on the first page, it has to walk through the entire report's secondary recordsource somehow. There is no magic way that it's going to know this at the opening of the report. Whether it's some internal Access function or whatever, that secondary data's got to be visited.

To explain the 'secondary' recordsource--Access reports are all 'subqueries' internally, in that the Group levels are based on an internal GroupBY query over whatever source table/query you have as the report's source. And that recordset has not been visited until you page through the report, or use your own subquery.

You can reference certain totals in report at the first page, but only if they're core sql functions on the base recordsource, like Sum() and Avg(). Say you have a field in the report header that references a 'grand total' field in the report footer--you'll get the right answer, but Access is running the Sum() over that field and has walked through only the source table/query.
--Jim

 

[AudreyCole]

Jim: Interesting musings about 'secondary' recordsources. That explains why, when I msgbox'ed the count from the report footer, it first gave me 0, then gave me the ultimate count. Thanks.

I've decided to write a little function that can be the control source for a counter on any report. If I use 2 parameters to pass in the recordsource of the report and the column I want to count, I can use a "select DISTINCT" to get the values, then pass the recordnumber to a variable and have the function return that number to me.

The overhead here is simply a single function used as the controlsource on each report that needs one or more counts on it. It's pretty simple code, so I think it should be pretty fast.

I'll keep you posted on my success with this technique.

Thanks again ever so much,

Audrey