Printing Multiple pages depending on # of items in report

[demchak]

I am trying to print out letters from a report that need to be mailed. I need to print out a varying number of letters depending on how many contacts are listed per account. The letters are grouped by account# with every contact for that account listed on the second page. In the exmple below I would like to print out 2 copies of the letter since there is 2 contacts. The number of contacts varies from 1-6.

Does anyone know if this is possible in access?
Thanks in advance for the help,
WH

ex
==============start of letter====pg1======
Re: Account 12345
bla bla bla
just to inform you this letter will
be sent to everyone included in schedule I
which is attatched
<<page break>>=========pg2===========
joe smith
123 nowhere st
whever MN 12345

john doe
23 whatever
nowhere tn 34356

=============end of letter=============

 

[KenReay]

Hi

You do not say what your table structure is, but assuming you have a 'Circulation' list table something like

PersonId
AccountId

then if you make the recordsource of the report a query based on a join between the Account Table and Circulation table (on AccountId), you will get a page per PersonId per Account

Regards

Ken Reay

 

[demchak]

My table structure is (in breif)

Accounts
Account# open_date Account_admin

Contacts
Account# first_name last_name

They are linked by account# with a one to many(one account many contacts)

The problem I am having is there are approx 200 accounts so the report prints 400 pages, the cover letter with the account# and the the second page with all of the contacts for that account.

Each account can have a different number of contacts.

I need each leter to print a different number of times depending on how many contacts are listed on the second page, but each copy must show all the contacts ie. in the first example the letter should print twice so joe smith would see a letter with both his name and john doe's name and visaversa for the second copy.

Some accounts only have 1 contact and some have up to 6 so I cant just photocopy the letters, short of checking each sheet and making the correct number of copies.

I hope this explains the problem better
Please let me know if you need more info.

Thanks again,
WH

 

[KenReay]

Hi

I assume the 'Letter' is an Access Report?

If it is not already, make the letter consist of a main report (based on a query which is a join of Accounts and Contacts joined on Account#), and a sub report of the contacts based on a query of the Contacts table with criteria of ContactId <> ContactId on Letter (assuming you do not to show current recipient on the CC list).

This should give you a letter for each recipient of the report, within it will be circulation list.

I am not clear from your description if the report and the lletr are to be printed in one 'pass', if yes, then make the 'letter' a section on the report, with start new page after section, so that you get letter,report,letter,report.

Regards

Ken Reay

 

[demchak]

Im sorry for being so unclear, let me try to lay it out a little better.
The report is an access report.
It is setup to be grouped by account number.
I have the letter in the <account# header> section
I have a page break at the end of that section then the <detil> section contains the contacts.
The report groups the contacts correctly and comes out like

=========page 1=========
re acct# 12345
body of letter
bla bla
this notice was sent to all parties listed
in SECTION I attatched
bla bla bla
=========page 2=========
SECTION I

contact1 associated with account 12345

contact2 associated with account 12345
========end of letter==========


It prints as letter1 report1 letter2 report2.

The problem is if there are multiple contacts on the report page I wanted multiple copies to print.

EX
If account1 has 2 contacts listed on the report1 and account 2 has 3 contacts and account 3 has 1 contact listed I was trying to get it to print like

letter1 report1 letter1 report1 letter2 report2 letter2 report2 letter2 report2 letter3 report3

I hope this makes things more clear. If not then I resign the problem to my inability to explain it.
.....at least I knew enough not to become a teacher i guess.


Thanks again for the help,
WH

 

[KenReay]

Hi

OK, that is what I thought, and

&quot;consist of a main report (based on a query which is a join of Accounts and Contacts joined on Account#), &quot;

from earlier post should mean that you get row in your query for each account/contact combination, which is effect your multiple copies

Regards


Ken Reay

 

[demchak]

I understand now. I was going about it the entire wrong way and looking at the report itself and not at the data. Dont know what I was thinking. I remade the queries to reflect the changes that you said back in the first response and it works fine.
Thanks for putting up with my stupidness,
WH