repeating a record x times in a query

[Chuck]

Hi, I have a somewhat unique problem (at least unique to me, and I've
been doing this for longer than I care to admit). I have a client that
needs to print cards onto perforated card stock (so multiple records
per page), and they need to print out x copies of the same information
onto the cards. So for instance I have a record that has a name,
address, id number and then a number for the number of copies of that
record that needs to be printed out.

I know I can do this programmatically with the report to print out x
number of copies per record, but this would waste several hundred pages
of card stock by printing out blank cards. It also just doesn't seem
like the cleanest most elegant way of accomplishing this.

Has anyone ever accomplished this? Transforming a query of
"Name", "Address", 3
"Name2", "Address2", 2

to:

"Name", "Address"
"Name", "Address"
"Name", "Address"
"Name2", "Address2"
"Name2", "Address2"

Anyone have any ideas?
Any and all help would be appreciated.

-Charles

[Chuck]

Okay, in (partial) answer to my own, I can create a new table with the
desires results with the following function:

Function make_rst()

Dim db As Database
Dim rst, rst_new As Recordset
Dim i As Integer
Set db = CurrentDb
Set rst = db.OpenRecordset("SELECT * FROM copy_test")
Set rst_new = db.OpenRecordset("destination")
While Not rst.EOF
For i = 0 To rst.copies
rst_new.AddNew
rst_new!Address = rst!Address
rst_new.Update
Next
rst.MoveNext
Wend

End Function

which sort of solves the problem, except that I really dislike "temp"
tables, and moving data around like this. Is there another solution
I'm overlooking?

[Justin Hoffman]

"Chuck" <us****@popquizhotshot.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi, I have a somewhat unique problem (at least unique to me, and I've
been doing this for longer than I care to admit). I have a client that
needs to print cards onto perforated card stock (so multiple records
per page), and they need to print out x copies of the same information
onto the cards. So for instance I have a record that has a name,
address, id number and then a number for the number of copies of that
record that needs to be printed out.

I know I can do this programmatically with the report to print out x
number of copies per record, but this would waste several hundred pages
of card stock by printing out blank cards. It also just doesn't seem
like the cleanest most elegant way of accomplishing this.

Has anyone ever accomplished this? Transforming a query of
"Name", "Address", 3
"Name2", "Address2", 2

to:

"Name", "Address"
"Name", "Address"
"Name", "Address"
"Name2", "Address2"
"Name2", "Address2"

Anyone have any ideas?
Any and all help would be appreciated.

-Charles

If the number of duplicates which are needed are not too large (and can be
guaranteed to be within a certain range) then you could achieve this using
no code at all with the help of another table:
tblCopy
Qty F1
1 1
2 1
2 2
3 1
3 2
3 3
4 1
4 2
4 3
4 4
etc

Then your query joins tblAddress.Qty to tblCopy.Qty so you get the correct
number of records.

Another way is to write some VBA within the report which forces the section
to be repeated until the correct number of copies is reached. This may be a
better solution, but the first has advantages too since you can use it for
the underlying query, whereas the second only works for reports.

[PC Datasheet]

From my files ----

Use the same methodology fir your cards!

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com

Print Multiple Labels Based Upon The Amount In A Column

Q I have a query that shows information about individual product items and
includes a column for quantity on hand for each item. Each item has a
different quantity on hand. for example:
ProductName QOH
item1 3
item2 2
item3 4
I would like to print labels, one for each of the items. In the above
scenario, I would need 9 labels and would like the labels for each item to
print consecutively. How do I design a query to read the "quantity on hand"
column and generate the appropriate number of records for each specific
item? For example, item1 has 3 in stock, the query would show:
item1
item1
item1
A Create a table (tblNums) with a single, numeric field (Num) and add
numbers(records) from 1 to your maximum QOH. Add this table to the report's
record source query with no join between the tables. This is called a
cartesian query. Set the criteria under the Num field to:
<=[QOH]
You will get 3 records for Item1, 2 records for Item2 and 4 records for
Item3.

"Chuck" <us****@popquizhotshot.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi, I have a somewhat unique problem (at least unique to me, and I've
been doing this for longer than I care to admit). I have a client that
needs to print cards onto perforated card stock (so multiple records
per page), and they need to print out x copies of the same information
onto the cards. So for instance I have a record that has a name,
address, id number and then a number for the number of copies of that
record that needs to be printed out.

I know I can do this programmatically with the report to print out x
number of copies per record, but this would waste several hundred pages
of card stock by printing out blank cards. It also just doesn't seem
like the cleanest most elegant way of accomplishing this.

Has anyone ever accomplished this? Transforming a query of
"Name", "Address", 3
"Name2", "Address2", 2

to:

"Name", "Address"
"Name", "Address"
"Name", "Address"
"Name2", "Address2"
"Name2", "Address2"

Anyone have any ideas?
Any and all help would be appreciated.

-Charles