L
Lele
My application prints work tickets to track items on an order. For example if
my order has 2 line items: 3 pillows and 2 bedskirts, I am able to print 5
tickets showing:1 of 3 pillows, 2 of 3 pillows, 3 of 3 pillows, 1 of 2
bedskirts, 2 of 2 bedskirts.
I was able to do this using code from MVP John Vinson, He suggested I set
up an auxiliary table called Num and create non equi join" query. It works
great. The entire post is reprinted below.
Is there a way to get a total for all items on the order and have the code
create a “order running total� In the above example, Each ticket would also
say: 1 of 5 total items, 2 of 5 total items etc? We just sent out an order
with the 3 “pillows†but forgot to include 2 “bedskirtsâ€. Unfortunately, it
wasn’t the first time.
Any help is greatly appreciated.
Lele
Here is John's post:
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.
my order has 2 line items: 3 pillows and 2 bedskirts, I am able to print 5
tickets showing:1 of 3 pillows, 2 of 3 pillows, 3 of 3 pillows, 1 of 2
bedskirts, 2 of 2 bedskirts.
I was able to do this using code from MVP John Vinson, He suggested I set
up an auxiliary table called Num and create non equi join" query. It works
great. The entire post is reprinted below.
Is there a way to get a total for all items on the order and have the code
create a “order running total� In the above example, Each ticket would also
say: 1 of 5 total items, 2 of 5 total items etc? We just sent out an order
with the 3 “pillows†but forgot to include 2 “bedskirtsâ€. Unfortunately, it
wasn’t the first time.
Any help is greatly appreciated.
Lele
Here is John's post:
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.