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.
ShadowITAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Rey Obrero (Capricorn1)Commented:
you will need VBA codes to do this.
a sample db with the same tale design from original table will help.
ShadowITAuthor 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
Dale FyeOwner, 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.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

Rey Obrero (Capricorn1)Commented:
how did you arrived at the values in column "AN" ?
ShadowITAuthor 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.
ShadowITAuthor Commented:
I am simply dividing the [Amount] column in the current record by 12 and assigning that value across all the other 11 records.
ShadowITAuthor Commented:
It's not interest, I am taking a lump summed amt and amortizing it across 12 months as milestones instead.
Rey Obrero (Capricorn1)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 ?
ShadowITAuthor 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.
Rey Obrero (Capricorn1)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
        vDate = DateAdd("m", j, dteStart)
        Debug.Print vDate
        With rs2
            .AddNew
                ![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]
                ![Purchasing Group] = rs![Purchasing Group]
                ![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
 

Open in new window

Database1.accdb
ShadowITAuthor 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
ShadowITAuthor Commented:
Yeah, ok I see it now, there's a null value in my Annualized Spend field.  That's what's causing this issue.
Rey Obrero (Capricorn1)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
			vDate = DateAdd("m", j, dteStart)
			Debug.Print vDate
			With rs2
				.AddNew
					![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]
					![Purchasing Group] = rs![Purchasing Group]
					![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

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ShadowITAuthor Commented:
Perfect!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.