[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
>