Accumulated Factors (A2003)

Topic

New Reply

Hi there,

One of our tables has one column with daily accumulation factors of the (1+i) kind. We must have Access create an additional column showing the accumulated accumulation factor from the first record (day 1) to the Nth record (day N). The relation is multiplicative, i.e.:

Cell 1: (1+i_1)
Cell 2: (1+i_1) (1+i_2)
…
Cell N: (1+i_1) (1+i_2) … (1+i_N)

where (1+i_P) is the accumulation factor corresponding to the Pth day. So what varies from cell to cell is the upper limit of the product.

This sure can be easily done with Excel, but is there a way it can be achieved in Access?

Thank you in advance

Reply | Quote

Replies

Does the table have an AutoNumber field or another kind of unique index that can be used to retrieve the factors in the correct order?

Reply | Quote

Hi Hans,

No, but we can create it if necessary. We can assure they’ll be in the correct order. Call this new field ID if you wish and assume it’s there .

Thanks

Reply | Quote

Put the following code in a standard module:

Public Function CumulativeProduct( _
TableName As String, _
FactorName As String, _
IndexName As String, _
IndexValue As Long) As Double
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
On Error GoTo ErrHandler
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset(“SELECT [” & FactorName & _
“] FROM [” & TableName & “] WHERE [” & IndexName & _
“] <= " & IndexValue, dbOpenDynaset)
CumulativeProduct = 1
Do While Not rst.EOF
CumulativeProduct = CumulativeProduct * _
(1 + rst.Fields(FactorName))
rst.MoveNext
Loop
ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function
ErrHandler:
Debug.Print Err.Description
CumulativeProduct = 1
Resume ExitHandler
End Function

Create a query based on your table, and add a calculated column

CumulativeFactor: CumulativeProduct("TableName","FactorName","ID",[ID])

Substitute the appropriate names between the quotes.

Reply | Quote

Hans,

Amazing!! Thank you so much!

I took some time to try it out and made one simple modification to your code: deleted the “1 +” preceding “rst.Fields(FactorName)”, since the field already contained factors instead of interest rates.

Now I’ll be able to work out similar formulas for accumulated sums and the like.

This is the resulting final code:

Public Function CumulativeProduct( _
TableName As String, _
FactorName As String, _
IndexName As String, _
IndexValue As Long) As Double
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
On Error GoTo ErrHandler
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [" & FactorName & _
"] FROM [" & TableName & "] WHERE [" & IndexName & _
"] <= " & IndexValue, dbOpenDynaset)
CumulativeProduct = 1
Do While Not rst.EOF
CumulativeProduct = CumulativeProduct * _
rst.Fields(FactorName)
rst.MoveNext
Loop
ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function
ErrHandler:
Debug.Print Err.Description
CumulativeProduct = 1
Resume ExitHandler
End Function


Thank you again.

Reply | Quote

For accumulated sums and averages, you can probably use the DSum and DAvg functions. Since there is no DProduct function, I wrote a custom version specific to your situation.

Reply | Quote

Great! I’ll check them out.

Unfortunately by the time I read your reply I had already worked on the cumulative sum function (based on your original CumulativeProduct), so I post it here for (at least my) future reference:

Public Function CumulativeSum( _
TableName As String, _
AddendName As String, _
IndexName As String, _
IndexValue As Long) As Double
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
On Error GoTo ErrHandler
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("SELECT [" & AddendName & _
"] FROM [" & TableName & "] WHERE [" & IndexName & _
"] <= " & IndexValue, dbOpenDynaset)
CumulativeSum = 0
Do While Not rst.EOF
CumulativeSum = CumulativeSum + _
rst.Fields(AddendName)
rst.MoveNext
Loop
ExitHandler:
On Error Resume Next
rst.Close
Set rst = Nothing
Set dbs = Nothing
Exit Function
ErrHandler:
Debug.Print Err.Description
CumulativeSum = 0
Resume ExitHandler
End Function

Thanks for your continuous support!

Reply | Quote

Just FYI, the DSum equivalent of this code would look like

DSum(“AddendName”,”TableName”,”ID<=" & [ID])

where you should substitute the appopriate names.

Reply | Quote

Alright. I’ll be studying the DSUM() formula in depth shortly.

Once again, thank you.

Reply | Quote