Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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?

    Thank you in advance
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #5
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #7
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accumulated Factors (A2003)

    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:

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

    Thanks for your continuous support!
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Accumulated Factors (A2003)

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

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

    where you should substitute the appopriate names.

  9. #9
    5 Star Lounger
    Join Date
    Mar 2002
    Location
    Buenos Aires, Argentina
    Posts
    877
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accumulated Factors (A2003)

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

    Once again, thank you.
    <img src=/w3timages/blue3line.gif width=33% height=2>
    <img src=/S/flags/Argentina.gif border=0 alt=Argentina width=30 height=18> <big><font color=4682b4><font face="Comic Sans MS">Diegol</font face=comic></font color=4682b4> </big>

Posting Permissions

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