Printing multiple copies based on the group in the report

  • Thread starter Thread starter Jane Schuster
  • Start date Start date
J

Jane Schuster

I have a one page status report (for 200 customers) that I send to each
customer every month, I've set the report up to print each page
alphabetically by customer.

Status Report

Page 1
Customer A
Status

Page 2
Customer B
Status

Now I have found out that some of my customers need more than one copy of
their status report. For example, customers G, M, and T need three copies
and Customers H and S need 5 copies. Is there any way to automate this?
 
Jane said:
I have a one page status report (for 200 customers) that I send to each
customer every month, I've set the report up to print each page
alphabetically by customer.

Status Report

Page 1
Customer A
Status

Page 2
Customer B
Status

Now I have found out that some of my customers need more than one copy of
their status report. For example, customers G, M, and T need three copies
and Customers H and S need 5 copies. Is there any way to automate this?
Click to expand...


Add an Integer field (named NumCopies) to the customers
table and set it to 3 for customers G, M and T and 5 for H
and S. You can leave it Null for all the other customers.

Then create a new table (named Numbers) with one field
(named Num). Populate the table with 1, 2, 3, ... up to
more copies than you will ever need.

Now, change the report's record source to a query like:

SELECT N.Num, C.CustomerName, C.Copies, . . .
FROM Customers As C LEFT JOIN Numbers As N
ON N.Num <= Nz(C.Copies, 1)

(Note: the query designer can not deal with that kind of
join so you must always edit that query in SQL view.)

If you want, you can even put the copy number on the report
by adding a text box with an expression like:

="Copy " + Num + " of " + Copies
 
Thank you so much. That works like a charm.

Marshall Barton said:
Add an Integer field (named NumCopies) to the customers
table and set it to 3 for customers G, M and T and 5 for H
and S. You can leave it Null for all the other customers.

Then create a new table (named Numbers) with one field
(named Num). Populate the table with 1, 2, 3, ... up to
more copies than you will ever need.

Now, change the report's record source to a query like:

SELECT N.Num, C.CustomerName, C.Copies, . . .
FROM Customers As C LEFT JOIN Numbers As N
ON N.Num <= Nz(C.Copies, 1)

(Note: the query designer can not deal with that kind of
join so you must always edit that query in SQL view.)

If you want, you can even put the copy number on the report
by adding a text box with an expression like:

="Copy " + Num + " of " + Copies
Click to expand...
 
Back
Top