Access Report Question

[Zwoker]

Hi all,

I have what appears to be a simple requirement in an MS Access (2003) report, but I just can't get it to work without it causing problems.

I have a table with data I want to report on, but only on the footer section for the (only) sort item on the table (also declared in the report). I don't want to print the detail section. What I do need to do is accumulate values from the data that is at the detail level. There is only one field there that is important, and that is a one-numeric that can contain the values 0-9. In the footer I want to display the number of each value that was found. E.g. "0" values occurred 4 times, "1" occurred 17 times etc...

I used the report wizard to produce the report, putting the items I wanted on the footer by hand. I then set the detail section visible property to No. That's when things started getting a little harder. Where do I put the code to accumulate counts of the detail level values? It seems that with the visible set to no, the print event code for the section is not called.
I tried setting the fields on the detail section to non-visible, and returning the section as a whole to visible, and putting code in the print event to accumulate the values I need for the footer, and once I set "can shrink" on everything, the output looked good, but then I noticed something very strange - the report looked fine in preview mode, but doubled some of the footer values when physically printed! I had even put the code inside an IF check for PrintCount=1 in case it was calling the detail section print event more than once, but it made no difference.

Now I'm sure that this is a reasonably common thing to want to do - not show detail level data, while making use of values that are stored at the detail level, but I have no idea what the "correct" solution is. Can anyone point me in the right direction?


Thanks.

[RyanT]

I may not be completely understanding your problem, but it sounds like it would make more sense if you would create a query that summarizes your data. Then, use that query as the source for your report.

Sorry if I oversimplified this.

[Zwoker]

I may not be completely understanding your problem, but it sounds like it would make more sense if you would create a query that summarizes your data. Then, use that query as the source for your report.

Sorry if I oversimplified this.

You are right, having the data summarised prior to reporting is a valid option.

It's just that several times I have found that data that I have either created, or have available to me, is what I need for later reporting, just at a more detailed level than is required.

I'd like to find out the correct way, in MS Access report terms, or reporting this data at a summary level. It appears on the surface that what I want to do should be both relatively easy and commonly done. Obviously more common would be just having totalling of values from the detail level, which I have done many times without incident. The issue I had this time was I needed to code for the count of the category that each detail record fell into, and was surprised at the apparent difficultly of finding a place to put the code that would be called when the detail frame was made non-visible.
Even more surprising was the issue of seeing a "good" result in print preview of the report output, but having it double some of the values when physically printed. It must be calling at least some of my code a second time, even though it is all inside PrintCount=1 conditions, and when I put break points in the code, they were not trigged when the report was printed - I'm guessing this last point is probably normal MS Access report behaviour.

Anyway, I'll play around with it a bit longer to try and understand the right way of doing it, and it it proves too hard I'll follow your suggestion and summerise the data in my VBA code prior to running the report.

Thanks.