How to generate records from an existing table with dates

[hans L]

I have an table that contains the following information:
ID START FINISH AMNT
A 01-APR-09 31-AUG-09 500
B 01-JUN-09 30-SEP-09 450
C 01-MAR-09 30-OCT-09 550
From this table I want to generate a cashflow curve with monthly increments.
To get there my existing table needs to be expanded to show 5 records for A,
4 records for B, and 8 records for C etc. From that table I could probably
apply a crosstab query. The question is how do I generate this expanded
table.
Any help is much appreciated.

 

[Piet Linden]

I have an table that contains the following information:
ID  START         FINISH        AMNT
A   01-APR-09   31-AUG-09    500
B   01-JUN-09    30-SEP-09    450
C   01-MAR-09   30-OCT-09   550
From this table I want to generate a cashflow curve with monthly increments.
 To get there my existing table needs to be expanded to show 5 records for A,
4 records for B, and 8 records for C etc.  From that table I could probably
apply a crosstab query.  The question is how do I generate this expanded
table.
Any help is much appreciated.

One way...
tblFromTo is the data above. (ID={A, B, C...})
TableOfDates has just one column, "TheDate", which is a date field.

SELECT tblFromTo.ID, TableOfDates.TheDate, tblFromTo.Amount
FROM tblFromTo, TableOfDates
WHERE (((tblFromTo.ID)="A") AND ((TableOfDates.TheDate) Between
[StartDate] And [FinishDate]));

Code to populate the table:
Public Sub PopulateDates(ByVal dtStart As Date, ByVal dtFinish As
Date)
Dim dtThis As Date
Dim strSQL As String

If dtStart >= dtFinish Then
MsgBox "Try making dtStart before dtFinish!"
Exit Sub
End If

For dtThis = dtStart To dtFinish
strSQL = "INSERT INTO TableOfDates(TheDate) VALUES (#" &
dtThis & "#);"
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next dtThis

MsgBox "done!"

End Sub

 

[hans L]

Thank you for your time Piet. I will give it a try.

I have an table that contains the following information:
ID START FINISH AMNT
A 01-APR-09 31-AUG-09 500
B 01-JUN-09 30-SEP-09 450
C 01-MAR-09 30-OCT-09 550
From this table I want to generate a cashflow curve with monthly increments.
To get there my existing table needs to be expanded to show 5 records for A,
4 records for B, and 8 records for C etc. From that table I could probably
apply a crosstab query. The question is how do I generate this expanded
table.
Any help is much appreciated.

One way...
tblFromTo is the data above. (ID={A, B, C...})
TableOfDates has just one column, "TheDate", which is a date field.

SELECT tblFromTo.ID, TableOfDates.TheDate, tblFromTo.Amount
FROM tblFromTo, TableOfDates
WHERE (((tblFromTo.ID)="A") AND ((TableOfDates.TheDate) Between
[StartDate] And [FinishDate]));

Code to populate the table:
Public Sub PopulateDates(ByVal dtStart As Date, ByVal dtFinish As
Date)
Dim dtThis As Date
Dim strSQL As String

If dtStart >= dtFinish Then
MsgBox "Try making dtStart before dtFinish!"
Exit Sub
End If

For dtThis = dtStart To dtFinish
strSQL = "INSERT INTO TableOfDates(TheDate) VALUES (#" &
dtThis & "#);"
DBEngine(0)(0).Execute strSQL, dbFailOnError
Next dtThis

MsgBox "done!"

End Sub