Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2007

    Question 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:

    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!


  2. #2
    Join Date
    May 2005
    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.
    Me.Geek = True

  3. #3
    Join Date
    Nov 2004
    out on a limb
    Provided Answers: 59
    you could 'always' do the calcualtion in the report as a last resort......
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2007

    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-
    Attached Files Attached Files

  5. #5
    Join Date
    May 2005
    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?
    Me.Geek = True

Posting Permissions

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