Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS Access: 12 Month Rolling Average

Status
Not open for further replies.
Drivium

Drivium

Technical User
Oct 6, 2011
46
US
Need to formulate a query that gives me a rolling 12 month average of well failures. This has been done in a spreadsheet through numerous calculations (attached), but I need to recreate this same concept in a query. Basically I need to recreate the data on the main graph tab. Thank you!

Table name: QRY_DATA_RP
Fields in table are: [Date], [Active Wells], [Failures]

And data in table looks like this:

DATE ACTIVE WELLS FAILURES [12 Months AVG]
1/1/2004 255 38 ??
2/1/2004 256 54 ??
3/1/2004 255 38 ??
4/1/2004 255 82 ??
5/1/2004 260 33 ??
6/1/2004 262 35 ??
7/1/2004 265 35 ??
8/1/2004 260 43 ??
9/1/2004 251 34 ??
10/1/2004 248 39 ??
11/1/2004 252 31 ??
12/1/2004 255 22 ??
1/1/2005 248 26 ??
2/1/2005 238 46 ??
3/1/2005 251 28 ??
4/1/2005 260 33 ??
5/1/2005 263 36 ??
6/1/2005 263 36 ??
7/1/2005 255 28 ??
8/1/2005 260 35 ??
9/1/2005 261 37 ??
10/1/2005 259 35 ??
11/1/2005 253 30 ??
12/1/2005 258 30 ??
1/1/2006 258 36 ??
 
Sort by date Sort by votes
Sorry, question marks belong under [12 months avg], formatted weird.
 
Upvote 0 Downvote
Thank you. So far, stuck on getting the autonumber thing to work. Sorry, rookie in Access. Not sure the linked example would apply as I have additional pieces of data in the mix (date, failures, wells) as opposed to the two pieces that are in the example.
 
Upvote 0 Downvote
When you say twelve month is it starting at Jan or any month you identify?
 
Upvote 0 Downvote
Code: 
SELECT 
  tblWellData.DtmDate, 
  tblWellData.ActiveWells, 
  tblWellData.Failures, 
  (Select sum(ActiveWells) from tblWellData as A where year([a].[dtmDate]) = year(tblWellData.dtmDate) AND A.dtmDate <= tblWellData.dtmDate) AS RunningWells, 
  (Select sum(Failures) from tblWellData as A where year([a].[dtmDate]) = year(tblWellData.dtmDate) AND A.dtmDate <= tblWellData.dtmDate) AS RunningFailures, 
  (Select Count(*)  from tblWellData as A where year([a].[dtmDate]) = year(tblWellData.dtmDate) AND A.dtmDate <= tblWellData.dtmDate) AS RunningCount,
  [RunningFailures]/[RunningCount] AS RunningAverage
FROM 
  tblWellData
ORDER BY 
  tblWellData.DtmDate;
Code: 
DtmDate	ActiveWells Failures RunningWells RunningFailures RunningCount RunningAverage
1/1/2004	255	38	255	38	1	38.00
2/1/2004	256	54	511	92	2	46.00
3/1/2004	255	38	766	130	3	43.33
4/1/2004	255	82	1021	212	4	53.00
5/1/2004	260	33	1281	245	5	49.00
6/1/2004	262	35	1543	280	6	46.67
7/1/2004	265	35	1808	315	7	45.00
8/1/2004	260	43	2068	358	8	44.75
9/1/2004	251	34	2319	392	9	43.56
10/1/2004	248	39	2567	431	10	43.10
11/1/2004	252	31	2819	462	11	42.00
12/1/2004	255	22	3074	484	12	40.33
1/1/2005	248	26	248	26	1	26.00
2/1/2005	238	46	486	72	2	36.00
3/1/2005	251	28	737	100	3	33.33
4/1/2005	260	33	997	133	4	33.25
5/1/2005	263	36	1260	169	5	33.80
6/1/2005	263	36	1523	205	6	34.17
7/1/2005	255	28	1778	233	7	33.29
8/1/2005	260	35	2038	268	8	33.50
9/1/2005	261	37	2299	305	9	33.89
10/1/2005	259	35	2558	340	10	34.00
11/1/2005	253	30	2811	370	11	33.64
12/1/2005	258	30	3069	400	12	33.33
1/1/2006	258	36	258	36	1	36.00
 
Upvote 0 Downvote
It's a rolling 12 months. The excel spreadsheet I pointed to SHOULD help to make sense of the logic (better than I can articulate),but for example on 1/1/2010, I need the avg for the months from 1/1/2009-1/1/2010 and on 2/1/2010 I need avg for the months from 2/1/2009-2/1/2010 and so on. I just bumps the 12 month range up by one with each passing month.
 
Upvote 0 Downvote
Try:
Code: 
SELECT QRY_DATA_RP.Date, QRY_DATA_RP.[Active Wells], QRY_DATA_RP.Failures, (SELECT Avg(Failures) FROM QRY_DATA_RP Q WHERE Q.[Date] BETWEEN DateAdd("yyyy",-1,QRY_DATA_RP.[Date]) AND QRY_DATA_RP.[Date]) AS 12MthAvg
FROM QRY_DATA_RP;

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Forgive me for continuing to point to the attached spreadsheet, but the results I'm aiming for should look like the following:

Date Avg Failure per well per year
1/1/2005 1.846755787
2/1/2005 1.826172516
3/1/2005 1.789162562
4/1/2005 1.593442623
5/1/2005 1.603668523
6/1/2005 1.607072692
7/1/2005 1.584756899
8/1/2005 1.553219448
9/1/2005 1.559921415
10/1/2005 1.538662316
11/1/2005 1.534246575
12/1/2005 1.56402737
1/1/2006 1.597921403
2/1/2006 1.539754363
3/1/2006 1.531997414
4/1/2006 1.511658031
5/1/2006 1.518494484
6/1/2006 1.459986988
7/1/2006 1.482926829
8/1/2006 1.468272047
9/1/2006 1.412682927
10/1/2006 1.326848249
11/1/2006 1.281703775
12/1/2006 1.266816865
1/1/2007 1.216164208
2/1/2007 1.162404092
3/1/2007 1.139216311
4/1/2007 1.158463004
5/1/2007 1.109211776
6/1/2007 1.137637795
7/1/2007 1.11994989
8/1/2007 1.081384471
9/1/2007 1.093623639
10/1/2007 1.121390872
11/1/2007 1.128363749
 
Upvote 0 Downvote
Taking an average of the active wells within the last 12 months from the sequential month and dividing by #of failures in the same 12 month period. That's the way the math is worked out in the spreadsheet.
 
Upvote 0 Downvote
Example from DATA tab on spreadsheet (maybe this will help paint the picture better than I am explaining):

Date| |Fails||Wells||SumOfFails||avgFperWperY| |Avg#OfActive|
Past12Mo WellsPast12mo

1/1/2006 36 258 410 1.597921403 256.5833333
2/1/2006 33 253 397 1.539754363 257.8333333
3/1/2006 26 251 395 1.531997414 257.8333333
4/1/2006 27 254 389 1.511658031 257.3333333
5/1/2006 37 257 390 1.518494484 256.8333333
6/1/2006 20 255 374 1.459986988 256.1666667
7/1/2006 34 256 380 1.482926829 256.25
8/1/2006 31 258 376 1.468272047 256.0833333
9/1/2006 23 263 362 1.412682927 256.25
10/1/2006 14 268 341 1.326848249 257
11/1/2006 20 268 331 1.281703775 258.25
12/1/2006 27 266 328 1.266816865 258.9166667
1/1/2007 24 269 316 1.216164208 259.8333333
2/1/2007 20 263 303 1.162404092 260.6666667
3/1/2007 21 262 298 1.139216311 261.5833333
4/1/2007 33 264 304 1.158463004 262.4166667
 
Upvote 0 Downvote
Your statement suggests the calculation is the Avgerage number of wells for the previous 12 months divided by the Sum of failures.

I still don't know where your numbers are coming from.

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Yes, that is exactly right. The numbers on the spreadsheet (and in the example I posted) are being generated and brought in from an Access dB initially. The numbers aren't manipulated until they reach the spreadsheet. I'm looking to bypass the spreadsheet and perform all of the necessary calculations and ultimately generated the graph within the dB.
 
Upvote 0 Downvote
This worked for me:
Code: 
SELECT QRY_DATA_RP.Date, QRY_DATA_RP.[Active Wells], QRY_DATA_RP.Failures, (SELECT Sum([Failures])/Avg([Active Wells]) FROM QRY_DATA_RP Q WHERE Q.[Date] BETWEEN DateAdd("yyyy",-1,QRY_DATA_RP.[Date]+1) AND QRY_DATA_RP.[Date]) AS 12MthAvg, (SELECT Sum([Failures]) FROM QRY_DATA_RP Q WHERE Q.[Date] BETWEEN DateAdd("yyyy",-1,QRY_DATA_RP.[Date]+1) AND QRY_DATA_RP.[Date]) AS SumFails, (SELECT Avg([Active Wells]) FROM QRY_DATA_RP Q WHERE Q.[Date] BETWEEN DateAdd("yyyy",-1,QRY_DATA_RP.[Date]+1) AND QRY_DATA_RP.[Date]) AS AvgWells
FROM QRY_DATA_RP;
[tt]
Date Wells Failures 12MthAvg SumFails AvgWells
1/1/2006 258 36 1.59 410 256.58
2/1/2006 253 33 1.53 397 257.83
3/1/2006 251 26 1.53 395 257.83
4/1/2006 254 27 1.51 389 257.33
5/1/2006 257 37 1.51 390 256.83
6/1/2006 255 20 1.45 374 256.16
7/1/2006 256 34 1.48 380 256.25
8/1/2006 258 31 1.46 376 256.08
9/1/2006 263 23 1.41 362 256.25
10/1/2006 268 14 1.32 341 257.00
11/1/2006 268 20 1.28 331 258.25
12/1/2006 266 27 1.26 328 258.91
1/1/2007 269 24 1.21 316 259.83
2/1/2007 263 20 1.16 303 260.66
3/1/2007 262 21 1.13 298 261.58
4/1/2007 264 33 1.15 304 262.41
[/tt]

Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
Date| |Fails||Wells||SumOfFails||avgFperWperY| |Avg#OfActive|
Past12Mo WellsPast12mo

4/1/2007 33 264 304 1.158463004 262.4166667

For example, the 304 above is a calculating the sum of failures for the previous 12 months adding all of the values together below.

Date Failures
5/1/2006 37
6/1/2006 20
7/1/2006 34
8/1/2006 31
9/1/2006 23
10/1/2006 14
11/1/2006 20
12/1/2006 27
1/1/2007 24
2/1/2007 20
3/1/2007 21
4/1/2007 33

The same concept applies to avg of active wells for the same period.
 
Upvote 0 Downvote
Query has been trying to run for about 5 min now. Seems promising though, especially with the results you posted. Thank you SO much for your help on this. I will post again when I receive the results. Any tips to make it run faster? My pc is pretty powerful and I'm even trying to run it locally thinking that might help speed it up, no such luck....still thinking...
 
Upvote 0 Downvote
Make sure the date field is indexed. Also, if you don't need the SumFails and AvgWells, remove them from the query.


If PHV is paying attention and has time, I expect he could do this without the subqueries using a self join.


Duane
Hook'D on Access
MS Access MVP
 
Upvote 0 Downvote
You, my man, are a genius. This works flawlessly. Thank you SO much. Made a couple of tweaks to my design and it sped right up.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Swi
  • Locked
  • Question
12 Month Rolling Average 2
Replies
15
Views
982
Swi
Swi
lameid
  • Locked
  • Question
SELECT FROM Table in Another Access File
Replies
2
Views
4K
lameid
lameid
JK Dutta
  • Locked
  • Question
requet for form an access query for report
Replies
2
Views
3K
dhookom
dhookom
monkeysee
  • Locked
  • Question
Automatic Invoicing in Access 1
Replies
6
Views
398
monkeysee
monkeysee
TheresAlwaysAWay
  • Locked
  • Question
Can not get Access 2016 queries to accept form value input or TempVars! input 2
2
Replies
21
Views
1K
TheresAlwaysAWay
TheresAlwaysAWay

Part and Inventory Search

Sponsor

Back
Top