How do I duplicate a row in a source query to (X) identical rows in a destination table when (X) is qty in the source query row

Looking for help in below table using VBA in Ms-Access 2007.

I have 39 columns to copy and repeat col40 times

Just starting to learn VBA. Any help would be appreciated.

I am attempting to take the following approach (by modifying another similar post that I found from a while back) which is giving me a few errors.

```
Sub DuplicateRecords()

Dim rstSource As Recordset
Dim rstDest As Recordset
Dim Dups As Integer

' use dbOpenSnapshot to open the source table READ-ONLY
Set rstSource = CurrentDb.OpenRecordset( _
"SELECT col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39, col40, col41 FROM Qry_Fleet_Plan_Builder;" _
, dbOpenSnapshot)
' use dbOpenDynaset to open the destination table READ-WRITE
Set rstDest = CurrentDb.OpenRecordset( _
"SELECT col2, col3, col4, col5, col6, col7, col8, col9, col10, col11, col12, col13, col14, col15, col16, col17, col18, col19, col20, col21, col22, col23, col24, col25, col26, col27, col28, col29, col30, col31, col32, col33, col34, col35, col36, col37, col38, col39 FROM BDFU;" _
, dbOpenDynaset)

With rstSource
' .BOF is Beginning of the table
' .EOF is End of the table
' Checking if both are false means there are records in the
' source table
If Not (.BOF And .EOF) Then
' get the first record from the source table
.MoveFirst
Do
' if col40 is NULL (empty)
If Nz(!col40, "") = "" Then
MsgBox ("Error! No Records in Source File")
Else ' if col40 IS NOT NULL
Dups = rstSource!col40
' loop through
For a = 1 To Dups
rstDest.AddNew
rstDest!col2 = rstSource!col2
rstDest!col3 = rstSource!col3

Create and populate Table 1 with a single column [col1]. You will populate that table with values 1, 2, 2, 3, 3, 3, 4, 4, 4, 4,...
Once you have that table the basic query is very simple, asumming your source is Table 2:

select  t2.[col2],t2.[col3],t2.[col4], ...
from [dbo].[Table2] as t2
left join [dbo].[Table1] as t1
on t2.[col40] = t1.[col1]
order by t2.[col1]

A more elegant way is given by Martin Smith in the comments:
Create and populate Table 3 with a single column [col1]. You will populate that table with unique values 1, 2, 3, 4,...The query then would be:

select  t2.[col2],t2.[col3],t2.[col4]
from [dbo].[Table2] as t2
left join [dbo].[Table3] as t3
on t2.[col40] >= t3.[col1]
order by t2.[col1]

Having these basic database approaches, you can then use VBA to create, populate the tables, and run the queries. Good luck!

StackExchange Database Administrators Q#149469, answer score: 2