How to duplicate records in a query for report purposes

[PJSPS8]

Hi all,

Hve a table with the following fields:

ID
ADRESS
#APARTMENTS

Need to do a mailing that prints for the same ADRESS as many copies as #APARTMENTS

I was thinking in making a temporary table from a query which duplicates each as #APARTMENTS but don't know how to do it.

What is the best way to do it?

Tks in adv
Paulo

 

[plog]

I've done this using a temporary table using VBA. In VBA you create a recordset to read in your apartment table. Then for every record you get the #APARTMENTS value and create a loop that runs that many times. Inside that loop you execute an INSERT query that adds the record from the apartments table to your temporary table. Last, base your mailing labels off that temporary table.

 

[dnlbrky]

This may not be the best way to do it, but it should work...

1. Create a table called "_Base10". Name the field "Number" (and might as well make it a primary key), and add the numbers 0 through 9.
   
   
   Number
   -------
   0
   1
   2
   3
   4
   5
   6
   7
   8
   9​
2. Create a query called "001_0to99" with the following SQL:
   

   SELECT CInt([Digit1]![Number] & [Digit2]![Number]) AS [Number]
   FROM _Base10 AS Digit1, _Base10 AS Digit2
   ORDER BY CInt([Digit1]![Number] & [Digit2]![Number]);

3. Create a query called "002_#Apartments" with the following SQL:
   

   SELECT [#Apartments].Number AS [#Apartments], [ApartmentID#].Number AS [ApartmentID#]
   FROM 001_0to99 AS [#Apartments], 001_0to99 AS [ApartmentID#]
   WHERE ((([ApartmentID#].Number)<=[#Apartments]![Number] And ([ApartmentID#].Number)<>0))
   ORDER BY [#Apartments].Number, [ApartmentID#].Number;

4. Finally, create a query called "003_MyTableWith#Apartments" with the following SQL (use your table's name in place of "MyTable"):
   

   SELECT MyTable.*, [002_#Apartments].[ApartmentID#]
   FROM MyTable LEFT JOIN [002_#Apartments] ON MyTable.[#Apartments] = [002_#Apartments].[#Apartments]
   WHERE (((MyTable.[#Apartments]) Is Not Null And (MyTable.[#Apartments])<>0))
   ORDER BY MyTable.ID, [002_#Apartments].[ApartmentID#];

Run the third query, and it should repeat each row in your original table (aka "MyTable") as many times as the value in the #Apartments field. If there are null or 0 or more than 99 apartments, the record will not appear in the query results.

 

[PJSPS8]

@bnlbrky

Thank you very much. May be it's not best way to do it but it worked perfectly.

 

[dnlbrky]

No problem, PJSPS8. Glad I could help.