Repeat record query 4

[TechnicalLoser]

Hello,
I do not know the best way of doing this so I will ask.
I have a database that contains my products, if someone orders my products I enter a quantity of how many of each product they order. From there I compile their order together in a query. How do I query their order query and say if they ordered 5 of one item it repeats that record 5 times into the newset query output. The reason I need it repeated by the way is because then I put it through a mail merge to print off labels for each item. Like I said I do not know if this is the best way to do it? I couldn't find a way to repeat a record on the Word side so I thought I would try on the Access side.

Thanks for any advice.
TL

 

[r937]

if you have an integers table, it's a piece of cake

create table integers (i integer)
insert into integers (i) values (1)
insert into integers (i) values (2)
insert into integers (i) values (3)
insert into integers (i) values (4)

populate this table with as many integers as the largest number of products that you think will ever be ordered -- i.e. if somebody's going to order three hundred and twenty-seven gizmos, and you're going to print three hundred and twenty-seven lables, then make sure you have all the integers in the table from 1 to 327

then your query is

[tt] select orders.productno
, 'part ' & i & ' of ' & orders.qtyordered
from orders, integers
where i between 1 and qtyordered
order by 1,2[/tt]

in case you're interested, this is called a theta join -- a join based not on equality but inequality

i'm sure you could produce the same result by writing some code, but i happen to like the integers table, because it has so many fine uses, and this is just another one

rudy

http://r937.com/

 

[TechnicalLoser]

Very slick, I like your idea and i see how it works for printing of the labels.
But my question is a little different. In yours if I made up an integer table to 327 it would make 327 records (or labels) but how can I make it check my QTY field and repeat that record as many times as in the QTY field.

I will create the actual labels in word using mail merge.

Maybe I am misunderstanding you? I built a table named integers with fields named integer and value (is this right) I made it go from 1 to 3. I entered your SQL and when I run it, it prompts me for a value for i(say I entered 5). When I did it outputted -
part 5 of 18
part 5 of 18
part 5 of 18
Which the 18 is right, it is the value in my QTY field. But I need it to repeat the record 18 times (or however many are in the QTY field) not how many is in the integer table. Basically I need it to be dynamic and read the QTY field for each record then repeat into a new table that many times.

I hope I haven't confused you?
Thank you very much for your help.

TL

 

[r937]

>> how can I make it check my QTY field and repeat
>> that record as many times as in the QTY field.

with this WHERE clause --

where i between 1 and qtyordered

>> when I run it, it prompts me for a value for i

no, i is the name of the column

check my create table syntax again

>> Basically I need it to be dynamic and read
>> the QTY field for each record

it will, trust me -- try it again, and be careful with your cut & paste


rudy

 

[danvlas]

Rudy, have one from me.

I have been using a table of dates in the same manner...

Select YourQuery.* From YourQuery, IntegersTable Where Integers.IntNumber <= YourQuery.Qty

will repeat all records in your query n times, n being the value in Qty

HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[r937]

thanks, dan

yeah, i have to use between 1 and qty because my integers table includes 0 and some negatives as well

 

[TechnicalLoser]

Got it working last night Rudy,

Thanks for your help,

TL