Number of Printed copies varies by value in Quantity field.

[Guest]

I routinely print labels to pack our products for shipping. If I need to
pack 10 items for an order, each in its own bag, I use a table called "num"
to create 10 labels for me, each showing: 1 of 10, 2 of 10, etc. The help
came from John Vinson, and it has be very useful. Now if possible I would
like to refine it a bit more.

Occassionally, based on the size of the items I could pack them in 2's, so
I would want a total of 5 labels, printed as 1-2 of 10, 3-4 of 10 etc. or
even in groups of 5 creating just 2 labels 1-5 of 10 and 6-10 of 10.

I would like to add a field to my label which asks the user for number in a
package. The program would then do the math as discribed above and create
the appropriate number of labels. Can this done? Any help is greatly
appreciated.

Lele

I have reprinted John's original response below:

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

 

[Allen Browne]

You probably have a field that indicates how many items fit in a pack, so
you can get the number of labels you need by typing an expression like this
into a fresh column of the Field row in query design:
NumberOfLabels: [Quantity] / [ItemsInPack])

That needs tidying up a bit, so it avoids the "Divide by Zero" error,
defaults to 1 id ItemsInPack is Null, and rounds any fraction upwards, so it
will probably become:
NumberOfLabels: -Int(-(Nz([Quantity],1) / IIf([ItemsInPack]=0, 1,
Nz([ItemsInPack], 1))))

 

[Guest]

Thanks for the help Allen. Your suggestions will work to calculate the
number of labels I need, but I have 2 remaining issues:
First I need system to actually print out the appropriate number of labels
and then I want each label numbered to reflect the number of items in the
bag and out of the total order, This means 10 items in 2 bags would create
two labels. The first would read 1-5 of 10 and the second would read 6-10 of
10

I have been sucessfully using a table called "n" which via a query

--
Lele

You probably have a field that indicates how many items fit in a pack, so
you can get the number of labels you need by typing an expression like this
into a fresh column of the Field row in query design:
NumberOfLabels: [Quantity] / [ItemsInPack])

That needs tidying up a bit, so it avoids the "Divide by Zero" error,
defaults to 1 id ItemsInPack is Null, and rounds any fraction upwards, so it
will probably become:
NumberOfLabels: -Int(-(Nz([Quantity],1) / IIf([ItemsInPack]=0, 1,
Nz([ItemsInPack], 1))))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I routinely print labels to pack our products for shipping. If I need to
pack 10 items for an order, each in its own bag, I use a table called
"num"
to create 10 labels for me, each showing: 1 of 10, 2 of 10, etc. The help
came from John Vinson, and it has be very useful. Now if possible I would
like to refine it a bit more.

Occassionally, based on the size of the items I could pack them in 2's,
so
I would want a total of 5 labels, printed as 1-2 of 10, 3-4 of 10 etc. or
even in groups of 5 creating just 2 labels 1-5 of 10 and 6-10 of 10.

I would like to add a field to my label which asks the user for number in
a
package. The program would then do the math as discribed above and create
the appropriate number of labels. Can this done? Any help is greatly
appreciated.

Lele

I have reprinted John's original response below:

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

 

[Guest]

Thanks for your help Allen. Your suggestion sounds good, but I am trying to
also get the program to print the appropriate number of labels based on both
quantity and the number in a pack. Furthermore, I want each label to show
the number out of the total quantity in each pack, for example if the qty was
10 and each pack could hold 5 items, I would want the system to generate 2
labels. The first would read 1-5 of 10 and the second would show 6-10 of 10.
What do you think. Can this be done.
--
Lele

You probably have a field that indicates how many items fit in a pack, so
you can get the number of labels you need by typing an expression like this
into a fresh column of the Field row in query design:
NumberOfLabels: [Quantity] / [ItemsInPack])

That needs tidying up a bit, so it avoids the "Divide by Zero" error,
defaults to 1 id ItemsInPack is Null, and rounds any fraction upwards, so it
will probably become:
NumberOfLabels: -Int(-(Nz([Quantity],1) / IIf([ItemsInPack]=0, 1,
Nz([ItemsInPack], 1))))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I routinely print labels to pack our products for shipping. If I need to
pack 10 items for an order, each in its own bag, I use a table called
"num"
to create 10 labels for me, each showing: 1 of 10, 2 of 10, etc. The help
came from John Vinson, and it has be very useful. Now if possible I would
like to refine it a bit more.

Occassionally, based on the size of the items I could pack them in 2's,
so
I would want a total of 5 labels, printed as 1-2 of 10, 3-4 of 10 etc. or
even in groups of 5 creating just 2 labels 1-5 of 10 and 6-10 of 10.

I would like to add a field to my label which asks the user for number in
a
package. The program would then do the math as discribed above and create
the appropriate number of labels. Can this done? Any help is greatly
appreciated.

Lele

I have reprinted John's original response below:

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

 

[Allen Browne]

See:
Print a Quantity of a Label
at:
http://allenbrowne.com/ser-39.html

The article explains how to use a counting table as John suggested to you to
generate a record for each label. It also illustrates how to print:
1 of 5
on the label.

If you want the label to print:
1-5 of 10
for the first of 2 packs of 5, you will need to adapt the idea further. You
will use the counter table for the number of *packs*, and then use an
expression to multiply the number of packs by the quantity in each pack to
get the right number. To to this, it will be best if the counter table
starts from zero (not 1). Use integer division (backslash operator), and add
1 to the result. It may take you some time to get the expression right, but
that's the approach to take.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks for your help Allen. Your suggestion sounds good, but I am trying
to
also get the program to print the appropriate number of labels based on
both
quantity and the number in a pack. Furthermore, I want each label to show
the number out of the total quantity in each pack, for example if the qty
was
10 and each pack could hold 5 items, I would want the system to generate 2
labels. The first would read 1-5 of 10 and the second would show 6-10 of
10.
What do you think. Can this be done.
--
Lele

You probably have a field that indicates how many items fit in a pack, so
you can get the number of labels you need by typing an expression like
this
into a fresh column of the Field row in query design:
NumberOfLabels: [Quantity] / [ItemsInPack])

That needs tidying up a bit, so it avoids the "Divide by Zero" error,
defaults to 1 id ItemsInPack is Null, and rounds any fraction upwards, so
it
will probably become:
NumberOfLabels: -Int(-(Nz([Quantity],1) / IIf([ItemsInPack]=0, 1,
Nz([ItemsInPack], 1))))

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I routinely print labels to pack our products for shipping. If I need
to
pack 10 items for an order, each in its own bag, I use a table called
"num"
to create 10 labels for me, each showing: 1 of 10, 2 of 10, etc. The
help
came from John Vinson, and it has be very useful. Now if possible I
would
like to refine it a bit more.

Occassionally, based on the size of the items I could pack them in
2's,
so
I would want a total of 5 labels, printed as 1-2 of 10, 3-4 of 10 etc.
or
even in groups of 5 creating just 2 labels 1-5 of 10 and 6-10 of 10.

I would like to add a field to my label which asks the user for number
in
a
package. The program would then do the math as discribed above and
create
the appropriate number of labels. Can this done? Any help is greatly
appreciated.

Lele

I have reprinted John's original response below:

An auxiliary table is handy for this purpose. I routinely put a table
named Num, with one Integer (or Long Integer) field N as its primary
key. I originally created the table (I just import it now) in Excel
with fill-down sequential, with numbers from 1 to 10000.

Create a Query for your report with whatever tables you need; include
the Num table with (initially) a Join from from the Quantity field to
N. Then open the query in SQL view and edit the line

INNER JOIN Num ON Num.N = yourtable.quantity

to

INNER JOIN Num ON Num.N <= yourtable.quantity

This "non equi join" query will give you as many repeats as the value
of quantity for each row. You can use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.