Recently I was asked to create a past due invoice and letter generator. I now have 2 separate working reports, one generating the dunning letters with details about past due invoices, and the other pulling the actual invoices referenced.

I tried to combine these by including the invoice report as a subreport in the letter generator report. The problem is that the invoice report has a page footer. When I drag it in as a subreport, it gets rid of the invoice page footer. I tried moving the invoice page footer up into the group footer but then there was no way to insure the footer stayed in a fixed position relative to the bottom of the invoice page, this is because the detail lines offset it depending on if more than one line item shows on the invoice.

Ideally, I'd just like to have it collate the two reports, sorting them by parent account number. There would always be one letter per account but there might be multiple invoices following that letter. I looked through some previous posts but I couldn't find anything on collating by a similar field. I'm sort of a noob with VB and have very minimal knowledge so far. Any advice would be appreciated, Thanks! Joshua

Just keep them as separate reports and run them both on clicking a button of some kind (normally an OK or Preview button in a reporting dialog). Each of the reports just needs to pick up the specification of the common field and limit it's results to only that.

I'm not sure if I understand what you mean. It sounds like you're suggesting I go in and limit the parent account number on the queries the 2 reports are pulling from and print off the letter and then the invoice(s). I already have both reports pulling all the desired dunning letters and invoices. If there were only a few this would be no problem but I'm dealing with hundreds.

What I need sounds ridiculously simple but I've not seen anything in access that will collate by parent account number. What I would like is a single report returning the following:

Parent account 1 dunning letter (from report 1)
Parent account 1 invoice(s) (from report 2)
Parent account 2 dunning letter (from report 1)
Parent account 2 invoice(s) (from report 2)
And would continue until final account.

Thanks

create a top report which does nothing except go through the query
allow grouping ont he customer number
in the detail section add the two reports as sub report to the new report.

healdem,
That's a good idea but I think I'd end up with the problem I had previously, It would either discard the subreport page footer (with the invoice totals), or I could move that subreport page footer into the detail footer but then it would not stay in a fixed position relative to the bottom of that page. Joshua

what happens if you insert the second report in the first reports report footer with a form feed immediately before it

Right now I have the second report in the first report's group footer and I put a page break right before it. This worked for starting the accompanying invoices on the next page, however the bottom of the sub-report (invoice) is still floating depending upon the number of details per page. It would work perfectly if somehow you could fix the sub-report's group footer to the bottom of the invoice pages.

theres a limit to what you can do in Access reports.
it sounds like you are on or near that limit.

one last fianl thing you could consider is laying out a new top level report as an A3 report, in the left hand side you put the first report, the right hand side you put the second report.

after that Im out of ideas. you may need to go to your users and tell 'em they can't have what they think they want. in all honesty unless they are seriously anal I suspect its nto a major issue for them. usually if you can present the information they need thats fine. I've only ever found it an issue when you have a prat as 'da man' in charge

Agreed there.

The problem is that we're going to be sending these to a group of customers who already look for every excuse not to pay their bills. We have a set format we have always used and unfortunately we are bound to that format, especially as my program is intended to generate re-prints of the original invoices, in which that detail is on the bottom. I can't believe there is nothing that can be done to collate two reports. That seems like such a simple task compared with other things Access is capeable of. Argh...

Don't blame Access,... it's definitely not a normal thing to try to do. You don't get reports and then start trying to "process" or collate them. That's just impossible.

The normal thing to do is to process your data from your tables and queries into a report structure that gives you the result you want.

I personally can't see the problem of just leaving your reports as they are (since you say they work perfectly) ; having "dunning letters" and "invoices" as separate reports, then using VBA to go through a recordset, looping through on this "common field", printing each report in turn with the value for the common field and then moving on to the next one.

IE (Pseudocode)

I don't know about it not a normal thing to do, I'm fairly new to access and use it to run multiple reports for multiple sales reps every period. So far I've been able to get by using sub-reports to combine reports. It seems like Microsoft knew the need to combine reports (as addressed with sub-reports), however, they did not anticipate the need to have a conditional page footer or fixed sub-report detail footer.

I'd be open to using VB, however, I don't know what I'm doing in it now. If you or someone could provide more detailed sample code I'd be very happy. Thanks!