Two reports per page--conditionally

[toadflax]

Hi,

I have a report in Access 2002 which generates receipts for payments.
The query it's based on pulls out records for payments between a given
date range and with an "email receipt" field being set to Yes. I
generate the report and then "print" it to PDF995, which gives me one
receipt per page of a multi-page PDF. I then break it into individual
PDFs with burstpdf for attachment to separate emails.

What I'd like to do is combine two receipts which are for the same
person ("member name", which is the primary key in the table) onto one
page before turning them into PDFs. The report only fills half a page,
so there's room, but I can't work out any way to make the report be a
single page for a single receipt/person but also for two receipts to
the same person. Perhaps I could have two queries and two reports, but
then I think the non-sequential numbers of the receipts from each
query would mess me up in what's already a rather too complicated
process!

Is there any way to make this happen? I'd be very grateful for any
help. I have probably explained this pretty badly, so please let me
know if you need more information.


Cheers,
Michelle

 

[Arvin Meyer [MVP]]

Add a group header and footer for the MemberID. Set the footer to force a
new page after the section (on the Format Tab) Set the MemberID to be in
both the Header and Detail sections but make them not visible. Adjust your
design to look proper with the new physical structure.

 

[toadflax]

Thanks Arvin, I didn't know about grouping like that. The only problem
now is that I need the receipts sorted in ascending numberic order
(i.e. by receipt number). Using your instructions I can get two
receipts for a single person on one page but the pages are sorted in
order of the surname. If I add "Receipt number" to sorting and
grouping above the name, it goes back to being sorted by receipt
number but the receipts are on single pages. What am I doing wrong?


Cheers,
Michelle

 

[Pat Hartman \(MVP\)]

Since a person can have multiple receipts, which receipt would be used to
determine the sort order? Do you see the problem with your request? Why do
you need the file sorted by receipt number? Wouldn't sorting it by member
work as well?

 

[Arvin Meyer [MVP]]

Open the Sorting and grouping dialog and add Receipt Number after the
MemberID. Do not add a Group Header or Footer. Now you should get the
MemberID, then the Receipt Number.

 

[toadflax]

Hmm, maybe it's not possible to do exactly what I want (sort by
ascending receipt number and then group together by surname *within*
that sort order)? Basically I may be creating a large batch of
receipts at any one time. I had been using the receipt number to
identify the PDF files once they were broken into single pages by
doing a batch rename, so I'd end up with say 30 files called
"CompanyXYZreceipt15001",
"CompanyXYZreceipt15002"..."CompanyXYZreceipt15030". I'd then match
the receipt number/s to the email message to the member (generated via
mail merge) and attach it, without having to open each one to see who
got which. It occurs to me that this won't work in one batch when two
receipts are on one page anyway, I'd have to rename in several batches
to get the numbers right (but that's reasonably easily done since the
double receipts tend to be the exception rather than the rule--it also
wouldn;t matter too much if the file is named only after the first
receipt number). Maybe I can just not worry too much about the file
names if I know that the receipts are alphabetical by surname since
the email address is usually based on surname as well. I'd just prefer
not to have to trade off between meaningful file names (so members
know what the attachment file is) and having two receipts on one page
(to save paper as requested by a few people).

My apologies, I know I'm explaining this badly and it all seems
convoluted. I inherited the record-keeping system in Access and I
think it kinda "evolved" rather than being designed, so I may be
asking too much.

Thanks very much for the help.


Cheers,
Michelle

 

[Arvin Meyer [MVP]]

You can have up to 10 group levels in a report, so all you need to do is
first group on receipt number, then group by surname.

 

[Pat Hartman \(MVP\)]

"sort by ascending receipt number and then group together by surname
*within* that sort order" ---
When your highest level sort field is unique, there is no grouping possible.
If you want to group by company, you need to use company as your high order
sort field. That is the only way you can get multiple receipts for the same
company to sort next to each other.

The process you describe does not require the records to be sorted by
receipt #, it requires them to be sorted by receipt # WITHIN customer.

 

[toadflax]

Pat, I think I understand what you're saying (if I group by receipt
number each "group" consists of one receipt so the further use of
surname within the group is redundant). The double receipts are always
consecutive but may be processed anywhere within the entire batch of
receipts e.g. receipts 001 and 002 go to one person, receipt 003 goes
to a second person, receipts004 and 005 to a third person. Ideally
what I'd like is:

receipt001
receipt002
-------------(page break)
receipt003
-------------(page break)
receipt004
receipt005
-------------(page break)

There's really no way for me to tell Access that if two consecutive
receipts are for the same person to put them on a single page without
it also reordering the records from the query? Sounds like not. I
appreciate all the help anyway.


Cheers,
Michelle