Printing specified numbers of labels

[cricketbird]

From selected record(s) in a listbox, I would like to print multiple mailing labels (stickers). So, if someone enters "5" on the form, it would print 5 identical labels per record. If they enter 300 labels, it should print 300 labels per selected record.

I can easily print 1 label each of the selected records, but I assume I need to join to an array or something with the numbers 1 through X or something to get the specified number of repeats. Does anyone have an example of how this would work? Thank you!

 

[theDBguy]

I would use a Tally table that contains sequential numbers and then use a cartesian join query to produce the number of labels/records you need. For example:

SELECT ClientName, Address FROM TableName, TallyTable WHERE TallyTable.Seq <= 5

Cartesian Query

When creating a query involving multiple tables, we can select from three types of table joins depending on the desired result of the query....

thedbguy.blogspot.com

 

[cricketbird]

I'm trying to decide if that is extraordinarily cludgy or extremely elegant, but it works, so I'll go with it. Thank you!

 

[theDBguy]

I'm trying to decide if that is extraordinarily cludgy or extremely elegant, but it works, so I'll go with it. Thank you!

Tally tables have a lot of use in a database, so you will hear it mentioned a lot. Good luck with your project.

 

[Gasman]

I'm trying to decide if that is extraordinarily cludgy or extremely elegant, but it works, so I'll go with it. Thank you!

Elegant. It is a novel way of using a cartesian join.

 

[cricketbird]

Elegant. It is a novel way of using a catesian join.

Awesome - never used them before. Thank you all!

 

[Pat Hartman]

Queries are not capable of generating records that are not the result of a join. If the join produces 100 rows, that's all you get. That leaves you with writing a VBA code loop - clunky and potentially slow, or using a tally table - efficient and almost certainly faster than a VBA code loop. Some people confuse efficient with "elegant". "Elegant" is meaningless. Efficient has meaning. I've used tally tables for a number of processes. Labels is the obvious and most common one. For example, you can generate a fixed number of labels per row by using a single variable in a form but by using a variable in each record of the recordset, you can generate a different number of labels for each record depending on the number in the record. Another use is for your tally table to contain dates rather than a sequence number. That allows you to generate a record for each date in a range which is very useful in a booking system or if you are doing something like generating a request for a mass on the first Monday of every month for a year.