Number of printed report copies varies by quantity & UOM

[Lele]

I need to print work tickets for our production people that reflect the
number of items to be produced. For example if my order is for 3 pillows and
2 bedskirts, I need to produce 5 work tickets.

I would like the 3 pillow tickets to show 1 of 3, 2 of 3, and 3 of 3.
Of course the 2 bedskirts would show 1 of 2, 2 of 2 etc. for each item on
the order.

I have been using an auxiliary table as suggested by MVP John Vinson. At
his suggestion I call it Num. It has one Integer (or Long Integer) field N
as its primary key. I then Create a Query for the report with my needed
tables; include the Num table with (initially) a Join from from the Quantity
field to
N. I 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 gives as many repeats as the value
of quantity for each row. I then use N as the control source of the
"1 of" textbox, and [quantity] as the control source of the "of 5"
textbox.

THis is all John Vinson's suggestion, and it works super. Thanks again to
you John.

My problem happens when I produce pairs of draperies. If the Unit of
measure (UOM) on the work order is pairs I need 2 copies of the work ticket
produced for each pair - one for left and one for the right panel.

I have created a field called pieces which uses an IIF
expression that doubles the QTY if the UOM is pairs. It looks like this
Pieces: IIf([UOM]="Pair(s)",[Qty]*2,[Qty])

I connected my N table to the pieces field and switched to SQL view to edit
the code to read <= . This works great and if I am producing
2 pairs I get 4 work tickets. Great!

Here is the problem.
The work tickets are now reading:

1 of 2 pair, 2 of 2 pair, 3 of 2 pair, 4 of 2 pair

I want the tickets to read as follows:


1 of 2 Pair , 1 of 2 Pair, 2 of 2 Pair, 2 of 2 Pair
Actually I really want the tickets to read

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2 pair-
Left)

I am very much hoping help is out there. I am close but really need help to
fully make this system operational for our staff.

Thanks so much.

 

[John Vinson]

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2 pair-
Left)

Ok... try

[N] \ 2 & " of " & [Qty] \ 2 & " Pair - " & IIF([N] MOD 2 = 0,
"Right", "Left")

John W. Vinson[MVP]

 

[Lele]

Hello John,

Thanks for your help. I am so ... close.
Now I am getting:

0 of 2 pairs - Left, 1 of 2 pairs - Right
1 of 2 pairs - Left, 2 of 2 pairs - Right

Thanks so much.
Lele

--
Lele

1 of 2 Pair - Right , 1 of 2 Pair - Left, 2 of 2 Pair - Right, 2 of 2 pair-
Left)

Ok... try

[N] \ 2 & " of " & [Qty] \ 2 & " Pair - " & IIF([N] MOD 2 = 0,
"Right", "Left")

John W. Vinson[MVP]

 

[John Vinson]

Hello John,

Thanks for your help. I am so ... close.
Now I am getting:

0 of 2 pairs - Left, 1 of 2 pairs - Right
1 of 2 pairs - Left, 2 of 2 pairs - Right

Thanks so much.
Lele

Ah! My mistake:

([N]+1) \ 2 & " of " & [Qty] \ 2 & " Pair - " & IIF([N] MOD 2 = 0,
"Right", "Left")

The \ operator is an integer divide, and the MOD operator returns the
remainder after a division. So if [N] is 1 or 2,

([N] + 1) \ 2

will be 2 / 2 = 1 or 3 / 2 = 1 (after truncating to the next lower
integer); and 1 MOD 2 is 1, 2 MOD 2 is 0, giving you the LEFT or RIGHT
respectively.

John W. Vinson[MVP]

 

[Lele]

The code works great! Thanks John. And thanks for the explanation, I had
just started working with the MOD function on another part of this project (I
was trying to recreate the Excel Round up function in Access). It is a
useful function and your explanation was helpful.

Lele

Hello John,

Thanks for your help. I am so ... close.
Now I am getting:

0 of 2 pairs - Left, 1 of 2 pairs - Right
1 of 2 pairs - Left, 2 of 2 pairs - Right

Thanks so much.
Lele

Ah! My mistake:

([N]+1) \ 2 & " of " & [Qty] \ 2 & " Pair - " & IIF([N] MOD 2 = 0,
"Right", "Left")

The \ operator is an integer divide, and the MOD operator returns the
remainder after a division. So if [N] is 1 or 2,

([N] + 1) \ 2

will be 2 / 2 = 1 or 3 / 2 = 1 (after truncating to the next lower
integer); and 1 MOD 2 is 1, 2 MOD 2 is 0, giving you the LEFT or RIGHT
respectively.

John W. Vinson[MVP]