# Creating 12 Duplicate Records from a Single Record

Hello Experts,

I have a dataset within a tbl and I need to duplicate this record 11 more times so that I have a total of 12 records.  Within the original record is an aggregate amount field that I need to divide by 12 and amortize across the newly created 12 records.  Each new record of the 12 represents a month so likewise I have a date field and I need to use that date as a starting date and calc out the need 11 months so that I have 12 records in total one for each month.

So I need to take a single record and duplicate it so I end up with 12 with differing dates and amounts.
###### Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
you will need VBA codes to do this.
a sample db with the same tale design from original table will help.
Author Commented:
Thanks for your reply, I've attached the dbase with a single record included and also an .xlsx of what the record should look like afterwards.  Please note columns "AM" and "AN" to discern the transformation.  I have ~400 records I need to do this so they'll need to be some sort of loop.
Database1.accdb
Book1.xlsx
Owner, Developing Solutions LLCCommented:
are you applying any interest across the additional 11 months?  Or are you simply dividing the [Amount] column in the current record by 12 and assigning that value across all the other 11 records.
Commented:
how did you arrived at the values in column "AN" ?
Author Commented:
Ah, you're right the amount is wrong it should be the original amount (1,270,000)/12=1058333.333.  I guess I forgot to hold the ctrl key down when I copied it.  Sorry about that.
Author Commented:
I am simply dividing the [Amount] column in the current record by 12 and assigning that value across all the other 11 records.
Author Commented:
It's not interest, I am taking a lump summed amt and amortizing it across 12 months as milestones instead.
Commented:
<I am simply dividing the [Amount] column in the current record by 12 and assigning that value across all the other 11 records. >

what about the First or the Twelfth  record, what should be the value? will it be updated to [amount]/12 ?
Author Commented:
Yes, it would likewise be updated to [amount]/12.  So you'd end up with only 12 records, not 13 with the 13th containing the original aggregated amount.
Commented:
ok, here I created another table sheet2
the codes to populate sheet2 is in module1

``````Sub addRecords()
Dim rs As DAO.Recordset, rs2 As DAO.Recordset, strField As String, j As Integer
Dim vDate As Date, dteStart As Date, dteEnd As Date, xAmount As Double
Set rs = CurrentDb.OpenRecordset("sheet1")
Set rs2 = CurrentDb.OpenRecordset("sheet2")

Do Until rs.EOF
xAmount = rs![Annualized Spend] / 12
dteStart = rs![Date]
For j = 0 To 11
Debug.Print vDate
With rs2
![Savings Name] = rs![Savings Name]
![Savings Description] = rs![Savings Description]
![Savings Start Date] = rs![Savings Start Date]
![Project Status] = rs![Project Status]
![Spend Type] = rs![Spend Type]
![Savings Type] = rs![Savings Type]
![Savings Frequency] = rs![Savings Frequency]
![Corporate CST] = rs![Corporate CST]
![Select Department/Team] = rs![Select Department/Team]
![SBU] = rs![SBU]
![Savings Categories] = rs![Savings Categories]
![Annualized Spend] = rs![Annualized Spend]
![Plant] = rs![Plant]
![Date] = vDate
![Savings Field] = xAmount
.Update
End With
Next

rs.MoveNext
Loop
rs.Close
rs2.Close
End Sub

``````
Database1.accdb
Author Commented:
This is close but it seems to need some error handling for null values I guess, not sure.  Please see attached errors.
Error1.jpg
Error2.jpg
Author Commented:
Yeah, ok I see it now, there's a null value in my Annualized Spend field.  That's what's causing this issue.
Commented:
try this revised codes

`````` Sub addRecords()
Dim rs As DAO.Recordset, rs2 As DAO.Recordset, strField As String, j As Integer
Dim vDate As Date, dteStart As Date, dteEnd As Date, xAmount As Double
Set rs = CurrentDb.OpenRecordset("sheet1")
Set rs2 = CurrentDb.OpenRecordset("sheet2")

Do Until rs.EOF
if Not IsNull(rs![Annualized Spend]) then
xAmount = rs![Annualized Spend] / 12
dteStart = rs![Date]
For j = 0 To 11
Debug.Print vDate
With rs2
![Savings Name] = rs![Savings Name]
![Savings Description] = rs![Savings Description]
![Savings Start Date] = rs![Savings Start Date]
![Project Status] = rs![Project Status]
![Spend Type] = rs![Spend Type]
![Savings Type] = rs![Savings Type]
![Savings Frequency] = rs![Savings Frequency]
![Corporate CST] = rs![Corporate CST]
![Select Department/Team] = rs![Select Department/Team]
![SBU] = rs![SBU]
![Savings Categories] = rs![Savings Categories]
![Annualized Spend] = rs![Annualized Spend]
![Plant] = rs![Plant]
![Date] = vDate
![Savings Field] = xAmount
.Update
End With
Next
end if

rs.MoveNext
Loop
rs.Close
rs2.Close
End Sub
``````

Experts Exchange Solution brought to you by