Report Problem: Check subreport values

[NachoMan]

Friends,
I have a problem with a report of mine (rptDailyReport). The report pulls data from three separate tables and therefore has three sections. The main section of the report pulls info from the Operations table (tblOps). The two subreports pull info from Special Operations (tblSpecOps) and Analysis tables (tblAnalysis). The two subreports are linked by a field called [DIRNum] which is autogenerated when new records of the three tables are created in their individual data entry forms. All of the three sections contain a field called [Classification]. Records in these three tables can be classified (lowest to highest) "Unclassified", "Official Use Only" or "Confidential."

I have a field in the header/footer of rptDailyReport which needs to show the overall classification of the report. The overall classification of the report would be the highest of these three classifications. The Operations and Analysis sections only have one record each per day and will almost always be "Unclassified." The SpecOps section is different. There can and usually are several records from the SpecOps section that will be pulled into the rptDailyReport. The classification for the SpecOps records will vary.

I need the report to somehow check the Classification field of all of the linked records returned in the SpecOps subreport and set the value of the [Classfication] field in the rptDailyReport footer/header to the highest of those classifications.
As an example: If the [Classification] field from the Operations and Analysis sections for today's report were both "Unclassified" and three of four records from the SpecOps table were "Unclassified", but the last one was "Confidential", then the overall report classification in the header/footer of rptDailyReport would indicate "Confidential."

Is this at all clear or did I beat it too far into the ground? I'm trying to be as clear as possible, but I wonder if I could have been a bit more to the point. Basically, I have no clue how to check/compare the values of the multiple records returned in the SpecOps subreport recordset.

Any ideas would be greatly appreciated.

 

[WayneRyan]

NachoMan,

I must admit that I didn't read your post in great detail, but
can make a few comments. The Security Folks take their
work seriously and you should approach this with a zero-
tolerance for errors.

If you can define your queries so that the Main Report's data
source has the overall classification. That would be perfect.

If that can't be done. I'd bite the bullet and apply DLookUps,
VBA code with recordsets, or something like that when the
report opens.

I had a similar situation with A97 and the Page Header
needed a classification. The Header was formatted and
printed long before the detail lines were traversed. With
variable numbers of lines per page, I had no choice but to
"run" the report twice. The sole purpose of the first pass
was to collect the Security Class for each page. In A2000
the events fire differently and the Header is not printed
until after the Detail Lines have been processed.

You might have an easier go of it, because you just need
a classification for the ENTIRE report.

hth,
Wayne