Getting Information From Subreports 1

[EllieFant]

I have a main report (PRPStatus) that gives the status of our employees in the Chemical Personnel Reliability Program from needing to fill out the NAC (National Agency Check), needing training, and pending certification each on their own subreport. These each (along with a couple others that aren't involved in my troubles here) give the details on how many personnel per department are missing their NACs, Missing Training, and Ready to be certified....no one person can be in two categories.

The three subreports are as follows:

SummaryMissingNAC
Field with number - CountOfNacMissing
Unbound Total Box - NACTotal

SummaryMissingTraining
Field with number - CountOfAccessType
Unbound Total Box - TrainingTotal

SummaryPendingCertification
Field with number - CountOfPendingCertification
Unbound Total Box - PendingTotal

Each of the unbound total boxes use the =Sum([Field Name]) to get the total.

I need to be able to put a box on my main report (PRPStatus) and have it give a summary:

Pending NAC 38
Pending Training 5
Pending Certification 88
Total 131


I am not sure how to go about getting the information out of the subreports and then totaling it up. I am sure it is possible, just not sure what direction I need to go to get it done. Can I do this right on the main report? Do I need to make another subreport with this information on it?

Thanks!
Ellie

Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

elliefant@qwest.net

 

[dhookom]

I would create three totals queries that are similar to your subreports record sources only grouped by Department. Then add these into your main report's recordsource and join them on the Department field. This allows you to place the totals values anywhere you want in the main report.

Duane
MS Access MVP

 

[CosmoKramer]

A simpler way would be to add text boxes to your main report and set as their control sources the already unbound total boxes from the subreports. For example:

=[SummaryMissingNAC].[Report]![NACTotal]

Then, add a fourth text box to display the sum of all three:

=[PendingNAC]+[PendingTraining]+[PendingCertification]

One downfall of cloning queries is if the criteria changes you have to remember to update all of the queries involved....


Hoc nomen meum verum non est.

 

[EllieFant]

The results I get from making a text box equal:

=[SummaryMissingNAC].[Report]![NACTotal]

Is #Name?

I used the build function (hadn't thought about using that - duh) and it seems to work with:

=[ ].[Report]![NACTotal]

Not sure what that means, but it worked.

I gave you a star cause if you hadn't of made your post, I wouldn't have thought about using build...thanks for making me think

Ellie
**Using Access 97 at work**
**Using Access 2000 at home**

elliefant@qwest.net

 

[CosmoKramer]

Ellie,

I was just providing you with an example of some syntax that has worked for me. I'm glad this worked for you, and good luck with the rest of the project!!!

Hoc nomen meum verum non est.