Split reports

[kmclane]

I have a report where the query got so big I couldn't run it any more. Since it was running onto two pages anyway, I split the query up. The only problem I have is that I need to use a total off the first report to calculate a total on the second report. I referenced the field on the first report, and if I preview it first then open the second report it grabs the data, but it seems to be using the same value for every page. This month each report is 66 pages long, with income on the first report and expense on the second. Each page of each report is for a separate agent. So page 1 of each report goes together. Is there a way to sync the reports without putting them back together so that the totinc field from the first report is pulled for matching records in the second report.
Thanks, Ken.

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[nagornyi]

Consider putting report data into some temp table and reporting off that table.

 

[kmclane]

That sounds ok, Exactly how would I go about that. I know how to turn my query into a make table, but that's not very temporary. However, since I only need to run these once a month, It might be worth it. Maybe I could put my report back together if it had a table for a recordsource. I'd like to know if there is a easy way to pull that data as I just spent two days redesigning everything. It would be easier if I can get it to work the way it is, but I'll do what I need to.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Well I started to play with is idea, but the main problem is that the totInc field is a calculated field that is the sum of twenty or so calculated fields. From a different query. I don't know if I can even start figuring that out. Maybe I can come up with a piece of code to pull the total off the coresponding page. I'm open to any other suggestions.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Well I tried this:

If Reports![SettlementReportPage1]![SortCode] = Me.SortCode Then
Me.TotInc = Reports![SettlementReportPage1]![SortCode]
End If

But it pulled the sort code. Not quite what I was looking for. I actully need something more like a query that runs while the reports are both open. Any suggestions would be welcome. Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[kmclane]

Ok I found the typo and changed it, but it still only gives me one amount for everypage. Strangely, it's from the second record. I have this in the detial on format event.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg

 

[nagornyi]

If you want to save two report structure, what about putting to the table only those data you need to transfer from first report to the second one?

 

[kmclane]

I was able to solve the problem by inserting my second form as a sub form of the first form. Now my numbers are correct and my report is in one piece again. Now I just need to figure out how to oull every two pages and output them to a pdf file. That fight is for another day. I was close when my report was still one page.
Ken

- If you are flammable and have legs, you are never blocking a fire exit.
Mitch Hedburg