Creating labels based on quantity fields

  • Thread starter Thread starter Tony Botelho
  • Start date Start date
T

Tony Botelho

I've been wrestling with this problem for a bit and I'm
hoping someone has run across something similar.

I have a table that contains items:

ItemID ItemName Qty
1 C01 4
2 C02 6
3 S01 2
4 A01 1

What I'd like to do is create a report that will print
one record on each page, BUT I want to print one record
for EACH item based on the actual Quantity. In other
words, I'd like to somehow get a query to return the
following results so that I can print a label for
each 'physical' item

ItemID ItemName New Field
1 C01 1 of 4
1 C01 2 of 4
1 C01 3 of 4
1 C01 4 of 4
2 C02 1 of 6
....
4 A01 1 of 1

Is this possible?

Thanks in advance
Tony
 
See:
Printing a Quantity of a Label
at:
http://allenbrowne.com/ser-39.html

The article explains how to create a query that contains a record for
however many you specify in the Qty field, and why this is more reliable
that using the events of the report.
 
Tony said:
I've been wrestling with this problem for a bit and I'm
hoping someone has run across something similar.

I have a table that contains items:

ItemID ItemName Qty
1 C01 4
2 C02 6
3 S01 2
4 A01 1

What I'd like to do is create a report that will print
one record on each page, BUT I want to print one record
for EACH item based on the actual Quantity. In other
words, I'd like to somehow get a query to return the
following results so that I can print a label for
each 'physical' item

ItemID ItemName New Field
1 C01 1 of 4
1 C01 2 of 4
1 C01 3 of 4
1 C01 4 of 4
2 C02 1 of 6
...
4 A01 1 of 1
Click to expand...


First create a table named Numbers with one field named
NumCopies. Then populate the table with values 1, 2, 3, ...
up to more than the maximum number of Qty you may ever need.

Second, create a query with both the table for the report
and the Numbers table. Make sure there is no Join line
between the tables. Drag each of the fields needed by the
report from the report's table to the query's field list.
Also drag the NumCopies field to the field list and use a
Criteria of:
<= Qty

Finally, change the report RecordSource to use the query
instead of the table. Then add a text box to the report and
use the expression:
=NumCopies & " of " & Qty
 
Back
Top