Kaup, Chester
Chester_Kaup at kindermorgan.com
Wed Mar 13 11:16:18 CDT 2013
Thanks for the help. I will give it a try.
-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of David McAfee
Sent: Tuesday, March 12, 2013 5:20 PM
To: Access Developers discussion and problem solving
Subject: Re: [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
> >
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com