1. ## Accumulated Factors (A2003)

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?

2. ## Re: Accumulated Factors (A2003)

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?

3. ## Re: Accumulated Factors (A2003)

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 <img src=/S/wink.gif border=0 alt=wink width=15 height=15>.

Thanks

4. ## Re: Accumulated Factors (A2003)

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 <!t>[" & FactorName & _
"]<!/t> FROM <!t>[" & TableName & "]<!/t> WHERE <!t>[" & IndexName & _
"]<!/t> <= " & 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.

5. ## Re: Accumulated Factors (A2003)

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:

<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
</code>

Thank you again.

6. ## Re: Accumulated Factors (A2003)

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.

7. ## Re: Accumulated Factors (A2003)

Great! I'll check them out.

<code>Public Function CumulativeSum( _
TableName 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.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</code>

8. ## Re: Accumulated Factors (A2003)

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

where you should substitute the appopriate names.

9. ## Re: Accumulated Factors (A2003)

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

Once again, thank you.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•