Lookup a Sum field on a different Report

[Ebes1099]

I have 2 different reports that display data month by month. The first has membership and the 2nd has Revenue. On the bottom I sum up the membership and revenue for a total value from every record in the report. These Sum fields aren't stored in the DB anywhere. But they are calculated when the report is run.

I want to be able to look up these numbers in another report I'm writing so I can take Total Revenue for a month divided by membership to get a Revenue per Member figure. Since these fields aren't being stored in a table or query I can't use a Dlookup. (At least not that I know of). Any other ideas on how I can do this.

I should also mention that those 2 reports are being shown together on a parent form. So they are both subforms on 1 parent form if that will make any difference in me being able to lookup/reference to them.

 

[dhookom]

You are using reports as subforms? I've never thought this possible or probable.

Why can't you create a simple totals query to calculate the sum of membership and revenue? What's so special about the calculation in the report that it can't be performed in a query?

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

My fault, I meant to say they are child reports on a parent report.

 

[dhookom]

You can reference a value from a subreport on the main report with a text box and control source like:

   =srptCtrlName!Report!txtCtrlOnSubReport

If the subreport might not return any records, you need to use

   =IIf(srptCtrlName!Report.HasData, srptCtrlName!Report!txtCtrlOnSubReport,0)

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

I'm getting a #name? error when I use that code. I tried a couple other things to see how exactly this worked, and if I go into each report on its own and try and reference a field on another report it will only work when that other form is open.

But I don't see how this code doesn't work on my main report because I have the 3 subreports on that report. I'm trying to reference a field on the 1st and 2nd subreport from the 3rd one. So I know the first 2 are being loaded since they are shown right above the 3rd report that I'm getting the #Name? error on. Any ideas?

 

[dhookom]

Can you provide the significant information since you can see your report design and we can't?

Subreport names (not the name of the source object):

Control names from the subreports you want to reference:

Location of the controls you want to reference:

Other stuff I didn't think to ask:

Duane
Hook'D on Access
MS Access MVP

 

[Ebes1099]

I have 4 total reports. The first 3 are subreports on the 4th. The 1st report is Membership, the 2nd report is Revenue, and the 3rd is Average Revenue. Each report is broken down by Month and shows the data for 18 months. The data for each month comes from a query. Then I use a few Sum fields and sum things up for the year and the last half of the prior year, and total for each month from all groups added together.

On the 3rd report, (Avg Revenue), I want to access the fields TotalMbrs and TotalRev. TotalMbrs is the sum for each record on the 1st report adding all the members accross the year for 1 group. TotalRev is the same thing on the revenue report. It adds Jan-Dec of the current year for each group.

I want to take the TotalRev for that group and Divide it by the TotalMbrs to get the AverageRevenue for the year. (I can't just add up the Averages per month and divide by 12, so that's out).

I tried using subreportName!parentReportName!SumTextBoxName but it gives me the #Name? error like it can't find it.

 

[Ebes1099]

I also notice that when I do reference that field and I have the report open that I'm doing the lookup on that it will only give me the total sum for the last group. Which makes sense since it's only 1 text box on the report and that last group's value is the most current one. But how can I get the value from the groups above that?

 

[dhookom]

I didn't ask for names of fields from subreports since they are not significant. I did ask for control names. Generically, assuming a lot about your subreport names, to get the final txtTotal value from subreport control named srptTwo into a different subreport on the same main report, the expression would be:

   =Parent!srptTwo.Report!txtTotal

If you want something other than the final value, you are out of luck. You can generally get all of these values from subreports and stick them directly into the main report's record source.

Duane
Hook'D on Access
MS Access MVP