[AccessD] 3 month moving total

I probably did not make it clear. I am looking for a total each month of the current month and the prior 2 months. For example March will be a total of January, February and March. April will be a total of February, March and April. Thank all.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Tuesday, March 12, 2013 3:43 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] 3 month moving total

I don't think the intended results are correct.

You can get a result like this:

Well    YearNo    Qtr    OilSum
1    2012    1    32
1    2012    2    33
1    2012    3    12
2    2012    1    45
2    2012    2    24
2    2012    3    14

By Creating a Qtr table (you can also create a function to do this) such as:
PKID (AutoNumber)
Qtr (Number)
MonthNo (Number)

values should be:
1,1
1,2
1,3
2,4
2,5
2,6
3,7
3,8
3,9
4,10
4,11
4,12

Then create a query such as:
SELECT A.Well, A.YearNo, Qtr, SUM(Oil) AS OilSum  FROM (SELECT tblSomeTable.Well, tblSomeTable.SomeDate, tblSomeTable.Oil,
Year(SomeDate) AS YearNo, Month([SomeDate]) AS MonthNo FROM tblSomeTable) A INNER JOIN tblQtr B ON A.MonthNo = B.MonthNo GROUP BY Well, YearNo, Qtr ;

If you don't want to show incomplete quarters, you can add a column to tblQtr called QtrCounter and put a value of 1 in each row

Modify the SQL to:
SELECT A.Well, A.YearNo, B.Qtr, Sum(A.Oil) AS OilSum, Sum(B.QtrCounter) AS CtrSum FROM (SELECT tblSomeTable.Well, tblSomeTable.SomeDate, tblSomeTable.Oil,
Year(SomeDate) AS YearNo, Month([SomeDate]) AS MonthNo FROM tblSomeTable) AS A INNER JOIN tblQtr AS B ON A.MonthNo = B.MonthNo GROUP BY A.Well, A.YearNo, B.Qtr;

and your results show like this:
Well    YearNo    Qtr    OilSum    CtrSum
1    2012    1    32    3
1    2012    2    33    3
1    2012    3    12    1
2    2012    1    45    3
2    2012    2    24    3
2    2012    3    14    1

Now you can opt to filter out where CtrSum <3

SELECT A.Well, A.YearNo, B.Qtr, Sum(A.Oil) AS OilSum, Sum(B.QtrCounter) AS CtrSum FROM (SELECT tblSomeTable.Well, tblSomeTable.SomeDate, tblSomeTable.Oil,
Year(SomeDate) AS YearNo, Month([SomeDate]) AS MonthNo FROM tblSomeTable) AS A INNER JOIN tblQtr AS B ON A.MonthNo = B.MonthNo GROUP BY A.Well, A.YearNo, B.Qtr HAVING (((Sum(B.QtrCounter))=3));


Well    YearNo    Qtr    OilSum
1    2012    1    32
1    2012    2    33
2    2012    1    45
2    2012    2    24

HTH
David






On Tue, Mar 12, 2013 at 12:53 PM, Kaup, Chester < Chester_Kaup at kindermorgan.com> wrote:

> I was wondering if it is possible to create a 3 month moving total in 
> a query. I have had no luck in finding an answer in any searches. 
> Below is some sample data and what the results should be. Thanks for any ideas.
>
> SAMPLE DATA
> Well       Date                      Oil
> 1              1/1/2012              10
> 1              2/1/2012              14
> 1              3/1/2012              8
> 1              4/1/2012              11
> 1              5/1/2012              9
> 1              6/1/2012              13
> 1              7/1/2012              12
> 2              1/1/2012              7
> 2              2/1/2012              20
> 2              3/1/2012              18
> 2              4/1/2012              5
> 2              5/1/2012              9
> 2              6/1/2012              10
> 2              7/1/2012              14
>
> INTENDED RESULTS
> 1              3/1/2012              32
> 1              4/1/2012              33
> 1              5/1/2012              28
> 1              6/1/2012              33
> 1              7/1/2012              34
> 2              3/1/2012              45
> 2              4/1/2012              43
> etc
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com