Calculate a 12 month rolling average

I have a db that has a 12 month rolling that needs to be calculated on a db and then will be displayed.
I understand how to do this in excel easily but I need to display each rolling 12 for the last 12 months as well.
Example:
                  Current      Sep-04      Aug-04      Jul-04      Jun-04
                  14,599        1,405        1,169        1,018        1,338
                  15,519        1,507        1,267        1,094        1,432
% by Month            93.2%      92.3%      93.1%      93.4%
12 Month Rolling      94.1%      94.1%      94.2%      94.4%      94.7%
                              
LVL 1
tupaleeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
You can retrieve dates from the last 12 months by filtering with DateDiff():

  .. WHERE DateDiff("m", [YourDateField], Date()) <=12

Then use this in a crosstab query where you use month for column.

/gustav
0
tupaleeAuthor Commented:
I can also sort descending through the months and then pull the top 12

The above was an example from excel and I would like to do the same thing in access
0
tupaleeAuthor Commented:
The rolling 12 calculation is what I am having issues with
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Gustav BrockCIOCommented:
I see.
I guess you would need to create a series of queries and then assemble the results from these with a union query.

Or you could use subqueries to retrieve the averages - as shown here:

  http://www.dbforums.com/archive/index.php/t-814418.html

It's hard to be more specific with no details on table structure.

/gustav
0
tupaleeAuthor Commented:
that's what I was thinking (about the sub queries)


Month     Data1        Data2         DataAvg     DATA1      DATA2      DATAAvg   MthAvg     Rolling 12Avg
Sep-04      5      5      100.00%      11      12      91.67%      94.12%      
Aug-04      5      8      62.50%      9      9      100.00%      82.35%      
Jul-04      3      6      50.00%      6      8      75.00%      64.29%      
Jun-04      3      6      50.00%      12      13      92.31%      78.95%      
May-04      1      3      33.33%      12      14      85.71%      76.47%      
Apr-04      2      5      40.00%      11      11      100.00%      81.25%      
Mar-04      0      1      0.00%      5      6      83.33%      71.43%      
Feb-04                        5      5      100.00%            
Jan-04                                                            
Dec-03                                                            
Nov-03                                                            
Oct-03            

So the thought process is to take Sum(Data1 + DATA1) for last 12 and then divide by Sum(Data2+DATA2) for last 12 and do this for each month.

FYI: some of this data as seen above is fresh and therefore doesn't extned back 12 months                                          
0
tupaleeAuthor Commented:
Month     Data1        Data2         DataAvg     DATA1      DATA2      DATAAvg   MthAvg     Rolling 12Avg
Sep-04       5             5                100.00%     11             12         91.67%     94.12%    
Aug-04     5               8                   62.50%     9              9         100.00%     82.35%    
Jul-04       3               6                50.00%         6             8            75.00%     64.29%    
Jun-04     3                6                50.00%      12               13         92.31%     78.95%    
May-04     1               3                 33.33%      12             14         85.71%     76.47%    
Apr-04     2                5                40.00%      11             11         100.00%     81.25%    
Mar-04     0                1                  0.00%      5              6          83.33%     71.43%    
Feb-04                                                          5              5           100.00%          
Jan-04                                                  
Dec-03                                                  
Nov-03                                                  
Oct-03    

Sorry this probably make more since
0
Gustav BrockCIOCommented:
That could be something like this:

Month      DataA1      DataA2      DataAAvg      DataB1      DataB2      DataBAvg      MthAvg      Roll12Avg
01-09-2004      5      5      100,00%      11      12      91,67%      94,12%      78,41%
01-08-2004      5      8      62,50%      9      9      100,00%      82,35%      75,79%
01-07-2004      3      6      50,00%      6      8      75,00%      64,29%      74,48%
01-06-2004      3      6      50,00%      12      13      92,31%      78,95%      77,02%
01-05-2004      1      3      33,33%      12      14      85,71%      76,47%      76,38%
01-04-2004      2      5      40,00%      11      11      100,00%      81,25%      76,34%
01-03-2004      0      1      0,00%      5      6      83,33%      71,43%      71,43%
01-02-2004                        5      5      100,00%            
01-01-2004                                                
01-12-2003                                                
01-11-2003                                                
01-10-2003

from:

  SELECT
    Month,
    DataA1,
    DataA2,
    [DataA1]/[DataA2] AS DataAAvg,
    DataB1,
    DataB2,
    [DataB1]/[DataB2] AS DataBAvg,
    ([DataA1]+[DataB1])/([DataA2]+[DataB2]) AS MthAvg,
      (SELECT
        AVG(([DataA1]+[DataB1])/([DataA2]+[DataB2]))
      FROM
        tblStats As T
      WHERE
        DateDiff('m', T.Month, tblStats.Month) BETWEEN 0 AND 12) AS Roll12Avg
  FROM
    tblStats
  GROUP BY
    Month,
    DataA1,
    DataA2,
    DataB1,
    DataB2
  ORDER BY
    Month DESC;

Maybe this:

        DateDiff('m', T.Month, tblStats.Month) BETWEEN 0 AND 12) AS Roll12Avg

should be:

        DateDiff('m', T.Month, tblStats.Month) BETWEEN 0 AND 11) AS Roll12Avg

/gustav                                                
0
tupaleeAuthor Commented:

i am getting a syntax error and can't figure out where

SELECT TOP 12 [PPAP-Complaints].PPAP,
[PPAP-Complaints].[New Product On-time],
 [PPAP-Complaints].[New Product Total],
[New Product On-time]/[New Product Total] AS NewProductPercentage,
 [PPAP-Complaints].[Recertification On-time],
 [PPAP-Complaints].[Recertification Total],
[Recertification On-time]/[Recertification Total] AS [Recertification Percentage],
([New Product On-time]+[Recertification On-time])/([New Product Total]+[Recertification Total]) AS [Percentage On-Time],

(SELECT AVG(([New Product On-Time]+[Recertification On-time])/([New Product Total]+[Recertification Total])) FROM [PPAP-Complaints] as Rolling WHERE DateDiff('m', [PPAP-Complaints].PPAP, Date()) <=12 as Rolling12Avg)

FROM [PPAP-Complaints]
ORDER BY [PPAP-Complaints].PPAP DESC;
0
tupaleeAuthor Commented:
thank you for tryng to help me
0
Gustav BrockCIOCommented:
This line should read:

.. [PPAP-Complaints] as Rolling WHERE DateDiff('m', [PPAP-Complaints].PPAP, Date()) <=12) as Rolling12Avg

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.