[AccessD] 3 month moving total

SELECT qryBase.Well, qryBase.SomeDate, qryBase.OilSumm AS CurrOil,
Nz(PrevMonth.OilSumm,0) AS PrevOil, Nz([TwoMonthsAgo].[OilSumm],0) AS
PrevOil2, ([CurrOil]+[PrevOil]+[PrevOil2]) AS ThreeMonthOil
FROM (qryBase LEFT JOIN qryBase AS PrevMonth ON (qryBase.SomeDate =
PrevMonth.NextMonth) AND (qryBase.Well = PrevMonth.Well)) LEFT JOIN qryBase
AS TwoMonthsAgo ON (qryBase.SomeDate = TwoMonthsAgo.TwoMonths) AND
(qryBase.Well = TwoMonthsAgo.Well)
ORDER BY qryBase.Well, qryBase.SomeDate;


QryBase:
SELECT tblSomeTable.Well, tblSomeTable.SomeDate, Sum(tblSomeTable.Oil) AS
OilSumm, DateAdd("m",1,[SomeDate]) AS NextMonth, DateAdd("m",2,[SomeDate])
AS TwoMonths
FROM tblSomeTable
GROUP BY tblSomeTable.Well, tblSomeTable.SomeDate,
DateAdd("m",1,[SomeDate]), DateAdd("m",2,[SomeDate])
ORDER BY tblSomeTable.Well, tblSomeTable.SomeDate;


You could probably do something easier using Dsum, but this is what I could
spit out in the short time that I had.

D

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

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