Sometimes it is desired to repeat a record multiple times in a query. Perhaps the query is a recordsource for a report that is printing mailing labels where some duplication of labels is needed. One technique to do this is to have a table that specifies the number of repeats needed. This table is then joined to a simple table holding sequential numbers. The image below outlines the table and working query.
This works fine where the number of repeats doesn’t require a large table of sequential numbers. Also, for demonstration purposes here I used a table that contains the output data with the number of repeats needed. Typically you would not store a “Repitions” column in any permanent table using a temporary table instead where you would have the source table’s Primary Key value and the Repitions value and update the query appropriately. For example:
Just a quick note on temporary table use in Access before I leave this snippet. Creating and deleting tables in a production environment Access application can cause the .accdb/.accde/.mdb/.mde file size to swell. Compacting the database returns the file size to something closer to deployment size. A nice alternative is to create your temporary tables outside your application in a temporary Access file. See the details on Tony Toews’ valuable Access site.