Create Table With Multiple Monthly Splits From Single Record Date Range

[The Brown Growler]

Hi,

I was provided some code in response to a topic I created in the "Queries" forum where I asked the following:

Is it possible to create a table containing multiple records from a date range in a single record where the multiple records show the monthly splits of the single record date range?

For example, if a single record in a table contained the following values for the fields [RefID], [StartDate], [EndDate], [Cost] respectively:

GBP-08-AB24, 01/07/2008, 30/09/2008, £90

Can a query or module be used to create a new table containing the following records from the above single record. I have numerous single records in the original table where I would need each of them splitting into the monthly components as below:

GBP-08-AB24, Jul-08, £30
GBP-08-AB24, Aug-08, £30
GBP-08-AB24, Sep-08, £30

I was kindly provided the following code by forum member "ezfriend" but I am not sure how to incorporate the code into my database. My VB knowledge is practically non existent, so although I was kindly provided with the code I do not know how to use it.

Code:
Private Sub MyRS()
Dim rs as Recordset

Set rs = currentDB.OpenRecordset("Select * from tblMyRecord")

While rs.eof = False
DoThis rs!Code, rs!StartDate, rs!EndDate, rs!Amount / DateDiff("m",dtstart, dtend) + 1
rs.MoveNext
Loop

rs.close
set rs = nothing

End Sub

Public Sub DoThis(sCode As String, dtStart As Date, dtEnd As Date, dAmount As Double)

Dim dtDate As Date
Dim i As Long

dtDate = dtStart
i = 0

Do While dtDate <= dtEnd
'insert the first record
CurrentDb.Execute "INSERT INTO tbl1 (FIELD1, MY, AMOUNT) VALUES('" & sCode & "', '" & Month(dtDate) & "-" & Year(dtDate) & "'," & dAmount & ")"
i = i + 1
dtDate = DateAdd("m", i, dtStart)
Loop

End Sub

On reflection, my original topic would have been better located in the "Modules & VBA" section so I have deleted the original post in the "Queries" forum and relocated it here.

Any help to get the code set up in my database would be most appreciated.

Thx & Rgds

 

[namliam]

You should have just continued the thread in the Queries forum and asked "EZFriend" to please help you one step further...

I dont see a reason to have code here to create what you want... Queries can do the job just fine without creating an additional table with duplicate data.

See this thread by Tinynoo for a simular question, with some small addaptations you can do what you are wanting...
http://www.access-programmers.co.uk/forums/showthread.php?t=161396

Ask any questions here if you are unsure how to procede.

 

[The Brown Growler]

namliam,

Thx for the reply and reference to the topic using queries as a solution.

I have managed to get the single records to split into multiple monthly records by adapting the queries in the solution you provided for "Tinynoo".

However, I have not managed to get the [Cost] value to be divided into its monthly conponents. I currently have the full [Cost] value duplicated per monthly split rather than smaller monthly [Cost] values.

Rgds & Thx

 

[namliam]

Well the monthly cost is calculated already by the code supplied by EZFriend
[Amount] / DateDiff("m",[StartDate], [EndDate]) + 1
or
[Amount] / DateDiff("m",[StartDate], [EndDate] + 1)

Both work

Glad you got it working and in one go too, must admit that I am somewhat happily surprised... Good job!