# Thread: DSUM Function in Access..Urgent!

1. Registered User
Join Date
Jan 2007
Posts
2

## Unanswered: DSUM Function in Access..Urgent!

I am going absolutely crazy trying to calculate moving averages by groups in Access.

This is what I want to do:

Data:
Account_Id Balance Recon 3 Month MovSum 3 Month MovAvg 3 Month MovSum with Lag
100 1000 5/31/2006
100 1050 6/30/2006
100 1678 7/31/2006 3728 1242.666667
100 1000 8/31/2006 3728 1242.666667 3728
100 1200 9/30/2006 3878 1292.666667 3728
200 500 5/31/2006
200 550 6/30/2006
200 550 7/31/2006 1600 533.3333333
200 578 8/31/2006 1678 559.3333333 1600
200 600 9/30/2006 1728 576 1678
200 612 10/31/2006 1790 596.6666667 1728
200 615 11/30/2006 1827 609 1790

The first three columns form my data set. As you can see in the 4th column I'm creating a 3 month moving sum within each account_id(column1). The 5th column is the 3 mnth Moving averages for and the 6th column is the lagged 3 month moving average.

Something tells me that I could probably use functions like DSum DAvg to work this out. But I'm not able get it. The output should look like the one above. I know it is not that difficult. If someone could help me out with the query structure for Access urgently, I'd be super grateful!

-datagazer

2. Computer Monkey
Join Date
May 2005
Posts
1,191
Where are you using these calculated values? On a form? Report? It looks like you're trying to store them in a table, which is a big poo-poo from a dB designer standpoint (don't store calculated values, calculate and display them).

The syntax for the Dsum and Davg are given in Microsoft VB Help as
DSum(expr, domain, [criteria])
DAvg(expr, domain, [criteria])
They also give good examples to work from in the help.

Give us a little more info as to how you hope to use these calc'd values, where you use them, when you need to use them, etc.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
you could 'always' do the calcualtion in the report as a last resort......

4. Registered User
Join Date
Jan 2007
Posts
2
Guys,

Thanks for your replies. I would want to do exactly as you said, calculate the values and display them through a query. The stored data would really be the first 3 columns of the data. But my requirement would be to display them in the way I've outlined in the data, having done the account wise moving averages and the sums across the "Recon" field(dates, basically). I dug up the syntax and the examples, but I was not able to get this exact form of display. I'm sure that the solution to this is rather simple. I'm attaching the database(MA.mdb) in a zipped file also. Please refer to the only table(called "data") which is there in the database. If someone could build the simple query for me...

I would need to create this query for certain frequent updates. The output would be used as inputs in a certain other econometric model which I've built.

Thank you-
datagazer

5. Computer Monkey
Join Date
May 2005
Posts
1,191
How are you wanting to display the data? In a report I assume?

Do you want a 3 month sum for each record, where you sum up the last 90 days worth from the recon field for that record? Or just one 3 month sum from the day you pull up the report?

Same question for the 3 month average.

the 6th column is the lagged 3 month moving average.
Could you explain this more?

#### Posting Permissions

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