Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jun 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Running Average (MS Access 2002)

    I need to be able to compute a running average of the three pay periods prior to the most current one.

    Pay Periods run from 1 to 26 during any calendar year. At the beginning of a new year the most recent pay periods would be 2003-24, 2003-25 and 2003-26 if the current pay period is 2004-1. For the record, calendar year and pay period are two separate variables.

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

    Re: Running Average (MS Access 2002)

    Welcome to Woody's Lounge!

    See if MSKB article ACC2000: How to Compute Moving Averages in Visual Basic for Applications, or else the thread starting at <post#=370149>post 370149</post#> help you on your way. If you need more assistance, don't hesitate to post back.

  3. #3
    New Lounger
    Join Date
    Jun 2004
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Running Average (MS Access 2002)

    My data is doesn't correspond to a date function in that it is recorded not but by date but in two week intervals starting with the first full pay period that year. Secondly, I will ultimately have to upsize this database and wish to avoid visual basic. The visual basic file looked promising but it will take me some time to interpet it as I have never used visual basic and would very much like to stick with SQL.

    I did create a query that put the pay periods in order, most recent first. Is there a way I can select values 2-4 and average them? Perhaps an index could be created so the newest first record could always be number 1, the next newest number 2 and so on?

    Ideally I could have the query use the pay period value in question, find it on the index, and then take the three values behind it and compute an average but I think this would not be essential.

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

    Re: Running Average (MS Access 2002)

    It is probably possible to do this without VBA, using something similar to the method used in the database attached to <post#=370303>post 370303</post#>. If you could post a stripped down version of your database, we could tackle the problem directly.
    <UL><LI>Make a copy of the database and work with that.
    <LI>Remove all database objects (tables, queries, forms, reports, macros and modules) that are not relevant to the problem. We probably need to see only some tables, perhaps a query.
    <LI>In the remaining table(s), remove most records - leave only the minimum number necessary to demonstrate the problem.
    <LI>Remove or modify data of a confidential nature.
    <LI>Do a compact and repair (Tools/Database Utilities).
    <LI>Make a zip file containing the database; it should be below 100KB.
    <LI>If you have difficulties getting the zip file below 100 KB, save the database in Access 97 format and then zip it.
    <LI>Attach the zip file to a reply.[/list]

Posting Permissions

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