Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009

    Red face Unanswered: Do Calculation in Access by group

    I have a table in Access like this:

    Ticker Name Date Total Assets

    a 2/27/1987 ....

    b 2/27/1987 ....

    a 3/30/1987 ....

    c 3/30/1987 ....

    . . .
    . . .
    . . .

    What I'm trying to do now is for each "Ticker Name", get the average of the total assets at that time and one quarter ago and store it in a new column
    named "Ave_ttlassets".

    For example, for "5/31/1987" under the ticker name "a", I want to put into the new column "Ave_ttlassets" the average of ticker a's total assets on
    5/31/1987 and ticker a's total assets on 2/27/1987.

    If there's any missing value, just leave the new column "Ave_ttlassets" missing, too.

    Can anybody help me out? I've been struggling on this for a couple of days in vain...Thanks a lot in advance!

  2. #2
    Join Date
    Jul 2009
    Now I've built a function to generate a query for each observation, however, I don't know how to run it without saving the query, and I don't know if it's possible to run a separated query for each of the observation cause I have hundreds of millions of observations out there.

    The code I wrote are as follow yet it doesn't work out:

    Function Convert(pDate As Date, ttlAssets As Double, Tick As String)

    On Error GoTo Err_Handle
    Dim LYear As Integer
    Dim LMth As Integer
    Dim newdate As Date
    Dim strSQL As String
    Dim TName As String
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef

    Set MyDB = CurrentDb()

    newdate = DateAdd("q", -1, pDate)
    TName = Tick

    'Extract the year, month values from the date parameter backed one quarter
    LYear = DatePart("yyyy", newdate)
    LMth = DatePart("m", newdate)

    strSQL = "UPDATE North_America_Corp SET new_ttlAssets = " _
    & "(SELECT North_America_Corp.Total_Assets_Qtrly FROM North_America_Corp WHERE (Ticker_Hist=" & TName & ") " _
    & "AND (DatePart(" & Chr(34) & "yyyy" & Chr(34) & ",Price_Date)=" & LYear & ") AND (DatePart(" & Chr(34) & "m" & Chr(34) & ",Price_Date)=" & LMth & "))"

    Set qdef = MyDB.CreateQueryDef("Qry_Name", strSQL)

    DoCmd.OpenQuery qdef

    Exit Function

    MsgBox Err.Description
    Resume Exit_Handle

    End Function

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    personally I'd of used an SQL aggregation functions such as
    SELECT MyTable.EventDate, Avg([Assets]) AS AverageAssets
    FROM MyTable
    group by EventDate;
    mind you I wouldn't be updating a value such as this as its derived dataa and pretty easy to get from the raw data.

    as a bonus you could use a similar SQL to find, say the average assets over a week, month or any other time period you want.

    but each to their own
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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