Main Report not pulling correct info from Subreports 1

[TammyT]

I have a report based on a large table, so I have to pull the data in via subreports (can't have more than 255 data fields on a report).

The subreports work fine when opened on their own - I get the correct totals.

However, when I place the subreports into the main report's Detail (or any!) section, & then in the Report Footer try to get the Grand Totals, I just get the summary totals from the last record pulled.

Here's the set-up:

1. In the Report Footer for each subreport, I put a text box that pulls the Total I need from that subreport.

2. In the main report, I put a text box for each of the subreports - the first referencing that total box from Subreport1, the second referencing that total box from Subreport2. I put the text boxes in the Detail section (although I've even tried creating variuos other footers - same result)

3. In the main report's Report Footer, I put a second set of text boxes that simply refer to the text boxes in #2 above.

When I run the main report, the text boxes in the Report Footer only give the data from the last record pulled, instead of a grand total from all the records. (each of the totals from the subreports stand on their own - they don't get added together).

Does anyone know why it's only pulling the last record in the main report, when the subreports give a correct grand total when run on their own?

+Tammy

 

[THWatson]

Tammy
This is a continuation of the thread from Friday.

I'm not sure what's going on here. But try this...

In the Detail section...
You already have two text boxes - one for the total from the first subreport, the second for the total from the second subreport. Say they are called txtSub1 and txtSub2.
Put an additional text box, let's call it txtGrandTotal, that has the control source = txtSub1 + txtSub2

If that works, you could try the same thing in the Report footer, if you prefer the grand total to show there.

Let me know if that works.

Tom

 

[THWatson]

Tammy
Just had a thought. I'll give an example of the scenario, just using two records.

Scenario in the report
Record1
SubReport1 Total is 10
SubReport2 Total is 20

Record2
Subreport1 Total is 30
Subreport2 Total is 40

What Grand Total are you expecing to see on the page for each of the records? The Grand Total for each record, or the Grand Total for all records?
Using the approach we have been working on, the Grand Total for Record1 should be 30, and the Grand Total for Record2 should be 70.
Is that what you are wanting, and getting? This is normal.

Is the Grand Total you want to show 100?

Tom

 

[TammyT]

I was hoping you'd be watching! I started a new thread, though, just in case!

Okay, I don't want to add subs 1 & 2. Based on your second post, I'm expecting the grand total for Sub 1 to be 40, & sub 2 to be 60. Does that help? I'm totalling each sub individually, because Sub 1 gives me a total of yes's, & Sub 2 gives me a total of NA's. I don't want the yes's & NA's added together; I just want a grand total of all Yes's & all NA's - does it make more sense now?

Thanks for sticking with me!!! We'll get there, I'm sure!

+Tammy

 

[THWatson]

Tammy
Okay, now I see.

What you are going to have to do is create an additional text box with a Running Sum (over Group, I would think). And you may have to create a Group Footer and put the Running Sum in that section.

Twiddle around with that, and I'm sure you will get it.

I suddenly got called away, and won't be back until the middle of this afternoon. But I will check in again then to see how you made out.

Part of the problem is that you can't sum controls in a Report Footer.

Anyway, try what I suggested, and see how it goes.

As you say, we'll get there.

Tom

 

[TammyT]

Okay, so you're a genious!!!

I never knew about the Running Sum feature!!

Actually, all I had to do was to create a footer for the ReviewNumber group & move my 2 existing text boxes into it, from the Detail section of the main report; since they refer to existing total boxes, that worked fine - now the Grand Totals are correct!!

I can't thank you enough - this was really befuddling me!

Stay tuned - just 'cuz this one is solved doesn't mean I won't have another "stuck" spot soon!!

+Tammy

 

[THWatson]

Tammy
Glad you got it working.

I'll stay tuned. If you get stuck again, I'll try to help you get unstuck.

Tom