Migrating from Word mail merge to Access Reports

[Anthony Stever]

Hi folks,

I'm trying to help convert a fairly cumbersome MS Word<>MS Access mail merge
process to an "all Access" solution. The office has several packets for
clients that consist of 8-10 pages each of printed info that is customized
to the clients' own info via a typical mail merge. However, the process
keeps breaking, especially in terms of having to re-establish the link from
the main document to the data source and then filter it for the right
clients.

My thought was to move this to Access, create a form to filter the records
in an easier fashion than having someone manually look through the record
list (using a form for criteria) and then producing however many packets are
needed, fully "merged" and so on. For example...

pg 1 Client Encounter Form
pg 2 Client Action Items
pg 3 Office Information and Next Appointment
.... and so on for 8-10 pages.

I thought I'd put all of pages of one packet into one report and end up with
3 separate reports, one for each 8-10pg packet. That's when I bumped into
the 22" vertical size limit for an Access report.

My next thought was to make each page a separate report, which I could do
easily enough but then we end up having someone on staff having to collate
the packets. That is, I had thought about using a macro to print each of
the reports in succession and that's what leads to someone having to collate
the packets (i.e. print report 1 for 10-100 people, then print report 2 for
10-100 people and then someone has to go through 2 x 10-100 print jobs and
add report 1 to report 2 for person 1, then add report 1 to report 2 for
person 2, etc.)... because I don't know of a way in Access to make it
"perfect".

Am I stuck with that trade-off vs. living with the dysfunctional system or
are there other options I haven't considered?

Thanks in advance!

Anth

 

[John Spencer]

A report Section is limited to 22 inches in height
A report is limited to 200 inches in height
A sub-report put onto a report can grow.

SO,
Add a group to your report based on the client id and put some of your
controls in the group header and footer and some in the details section.
You can add another group based on the clien id to get an additional 44
inches of print space.

So with two group headers, two group footers, and a detail section you
should be able to get 110 inches of printing space.

Another option is to sub-divide your data into some sub reports and then set
the height of the sub-report controls to .5 inches or less with a page break
control between each sub-report. Turn on Can Grow for the sub-reports and
the section containing the sub-reports.

Hopefully this will give you some ideas on overcoming the 22 inch limit of a
section in a report.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Anthony Stever]

John, thanks for the reply but I just ran a test by creating two of the
packet pages as separate subreports and putting to subreport controls in one
master report. The CanGrow works fine but it still prints all clients'
"page 1" followed by all clients "page 2". Maybe I missed something about
your advice that would have resolved that?

Thanks again,

Anth

 

[John Spencer]

Hard to say what is going on.

Is the main report only showing basic client information and only ONE TIME.
That is, the main report should have ONE record and ONLY ONE records for
each client.

Are the subreports linked to the basic client information on the main
report?
-- Click on a sub report control and see if link child fields and link
Master fields have values in them. They should show something like ClientID
as the linking fields.


--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Anthony Stever]

John, that was the problem. You nailed it and made this work perfectly!

Thank you!!

Anthony