# Thread: Do Calculation in Access by group

1. Registered User
Join Date
Jul 2009
Posts
3

## 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. Registered User
Join Date
Jul 2009
Posts
3
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()

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_Handle:
Exit Function

Err_Handle:
MsgBox Err.Description
Resume Exit_Handle

End Function

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
personally I'd of used an SQL aggregation functions such as
Code:
```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

#### Posting Permissions

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