Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    moving sums or moving averages (2000 sr-3)

    Need to sum the number accidents for a four previous months (or compute a moving average) continuously from april 1996 until the end of time. The value of April 1996 would be the total number accidents from January 1996 to April 1996, the the value of May 1996 would be the sum of February thru May 1996, and the value of June 1996 would be the total of March 1996 thru June...... the value of April 2004 would be the total number accidents from January 2004 to April 2004.

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

    Re: moving sums or moving averages (2000 sr-3)

    What is the structure of the table you have? Are the records individual accidents with a date field, or do they contain aggregated data?

  3. #3
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: moving sums or moving averages (2000 sr-3)

    data is individual accidents, the objective is to do the analyis in grouped data

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

    Re: moving sums or moving averages (2000 sr-3)

    You need a separate table with months; I created a series of dates in Excel (using fill down) and imported it into Access.
    You can then create a query based on the accidents table and the months table. The tables are not joined in the query. Add the month from the months table and the accident date from the accidents table. Set criteria to restrict the accident date to the four month period corresponding to the month field.
    Change the query to a Totals query, and change the totals option for the accident date to Where. Add a field to count the records.

    In the attached demo, I have used an intermediary query to calculate the first and last day of each four month period. This is not strictly necessary, but makes things a bit easier.

    You can import tblMonths and qryMonths into your own database; you will have to create the other query yourself.
    Attached Files Attached Files

  5. #5
    3 Star Lounger
    Join Date
    Apr 2001
    Location
    Joliette, Quebec, Canada
    Posts
    290
    Thanks
    9
    Thanked 0 Times in 0 Posts

    Re: moving sums or moving averages (2000 sr-3)

    thanks the solution is so cool

Posting Permissions

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