3 Month Rolling Average

[Drivium]

Regarding well testing:

Posted 5 months worth of data in the attached spreadsheet. Basically I have 6 columns of data:
Well Name Test_Date Oil_Vol Water_Vol Gas_Vol Water_Cut

I need to calculate a rolling 3 month average per well on oil,water,gas, and water cut. Easy to do in Excel, but not sure how to do in Access.

Thank you!!!

 

[dhookom]

Taking your question and applying it to the ShipVia and Freight columns in the Orders table in Northwind. This query will display a rolling 3 month average of freight by shipvia:

SELECT Orders.OrderID, Orders.ShipVia, Orders.OrderDate, Orders.Freight, DateAdd("m",-3,[Orders].[OrderDate]) AS SinceDate, 
(SELECT AVG(Freight)
 FROM Orders O2
 WHERE O2.ShipVia = Orders.ShipVia AND O2.OrderDate BETWEEN DateAdd("M",-3,Orders.OrderDate) and Orders.OrderDate) AS AvgFreight
FROM Orders
ORDER BY Orders.ShipVia, Orders.OrderDate;

Duane
Hook'D on Access
MS Access MVP

 

[Drivium]

I must have applied the technique wrong... every well has the same average for the same time period. When the date moves up a day, the average changes (but is the same) for all wells still.

SELECT Well_Name, Test_Date, DateAdd("m",-3,[Test_Date]) AS SinceDate,
(SELECT AVG(Oil_Vol)
FROM 3Mon_Average_1
WHERE Oil_Vol = Oil_Vol AND Test_Date BETWEEN DateAdd("M",-3,Test_Date) and Test_Date) AS AvgVol
FROM 3Mon_Average_1
ORDER BY Test_Date;

 

[dhookom]

You missed the entire alias of the internal table name. Try:

SELECT Well_Name, Test_Date, DateAdd("m",-3,[Test_Date]) AS SinceDate, 
(SELECT AVG(Oil_Vol)
 FROM 3Mon_Average_1 B
 WHERE B.Oil_Vol = A.Oil_Vol AND
  B.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS AvgVol
FROM 3Mon_Average_1 A
ORDER BY Test_Date;

Duane
Hook'D on Access
MS Access MVP

 

[Drivium]

That worked. Now trying to apply it to the rest of my criteria and getting an error about an extra )

SELECT Well_Name, Test_Date, DateAdd("m",-3,[Test_Date]) AS SinceDate,
(SELECT AVG(Oil_Vol),AVG(Water_Vol), AVG(Gas_Vol),AVG(Water_Cut)
FROM 3Mon_Average_2 B
WHERE B.Oil_Vol = A.Oil_Vol AND B.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Oil_Vol,B.Water_Vol = A.Water_Vol AND B.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Vol,B.Gas_Vol = A.Gas_Vol AND B.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Gas_Vol,B.Water_Cut = A.Water_Cut AND B.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Cut
FROM 3Mon_Average_2 A
ORDER BY Test_Date;

 

[dhookom]

Your subqueries should return only a single value. Since you have separate fields for Oil, Water, and Gas you need separate subqueries for each.

Duane
Hook'D on Access
MS Access MVP

 

[Drivium]

Something like.... (not working) Got error saying "The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect"

SELECT Well_Name, Test_Date, DateAdd("m",-3,[Test_Date]) AS SinceDate,
(SELECT AVG(Oil_Vol)
FROM 3Mon_Average_2 B
WHERE B.Oil_Vol = A.Oil_Vol AND B.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Oil_Vol
(SELECT AVG(Water_Vol)
FROM 3Mon_Average_2 C
WHERE C.Water_Vol = A.Water_Vol AND C.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Vol
(SELECT AVG(Gas_Vol)
FROM 3Mon_Average_2 D
WHERE D.Gas_Vol = A.Gas_Vol AND D.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Gas_Vol
(SELECT AVG(Water_Cut)
FROM 3Mon_Average_2 E
WHERE E.Water_Cut = A.Oil_Vol AND E.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Cut
FROM 3Mon_Average_2 A
ORDER BY Test_Date;

 

[dhookom]

You are missing some commas after the subquery aliases. Each of the subqueries creates a column in the query and columns must have a comma between them.

Duane
Hook'D on Access
MS Access MVP

 

[Drivium]

No luck. I've tried putting commas after each (Select statement, but it's not working. I either get a syntax error or punctuation error...

 

[Drivium]

Ok...got it to work...sort of. But I'm getting the same problem as before where the data is not unique to each well. But instead, showing the same data for each well (depending on date)

SELECT A.Well_Name, A.Test_Date, A.Oil_Vol, A.Water_Vol, A.Gas_Vol, A.Water_Cut, DateAdd("m",-3,[Test_Date]) AS SinceDate, (SELECT AVG(Oil_Vol)
FROM 3Mon_Average_2 B
WHERE B.Oil_Vol = B.Oil_Vol AND A.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Oil_Vol, (SELECT AVG(Water_Vol)
FROM 3Mon_Average_2 C
WHERE C.Water_Vol = C.Water_Vol AND A.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Vol, (SELECT AVG(Gas_Vol)
FROM 3Mon_Average_2 D
WHERE D.Gas_Vol = D.Gas_Vol AND A.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Gas_Vol, (SELECT AVG(Water_Cut)
FROM 3Mon_Average_2 E
WHERE E.Water_Cut = E.Water_Cut AND A.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Cut
FROM 3Mon_Average_2 AS A
ORDER BY A.Test_Date;

 

[Drivium]

Also, I really need this to SHOW the following columns Well_Name, Test_Date, Oil_Vol, Water_Vol, Gas_Vol, Water_Cut, 3Mon_Avg_Oil_Vol, 3Mon_Date, 3Mon_Avg_Water_Vol, 3Mon_Avg_Gas_Vol, 3Mon_Avg_Water_Cut,Delta_Oil_Vol, Delta_Water_Vol, Delta_Gas_Vol, Delta_Water_Cut.

For the 3 month avg date, I need that date to show as of the prior month. So if the well effective date of the test is 9/1/2011, the 3Mon_Date should show as of 8/1/2011 (but calculating 3 month avg)

 

[dhookom]

In your most recent query, you haven't mixed the aliases correctly. For instance comparing a value to itself makes no sense:

WHERE B.Oil_Vol = B.Oil_Vol

needs to be

WHERE A.Oil_Vol = B.Oil_Vol

The dates also need to use syntax similar to my original SQL.

Duane
Hook'D on Access
MS Access MVP

 

[Drivium]

Thanks for the reply. I changed up the WHERE ?, but not understanding which date reference I should change or what to change it to. Not totally sure I applied your last suggestion correctly either. Now Oil volume and average oil volume are identical.

SELECT;
SELECT A.Well_Name, A.Test_Date, A.Oil_Vol, A.Water_Vol, A.Gas_Vol, A.Water_Cut, DateAdd("m",-3,[Test_Date]) AS SinceDate, (SELECT AVG(Oil_Vol)
FROM 3Mon_Average_2 B
WHERE A.Oil_Vol = B.Oil_Vol AND A.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Oil_Vol, (SELECT AVG(Water_Vol)
FROM 3Mon_Average_2 C
WHERE A.Water_Vol = C.Water_Vol AND A.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Vol, (SELECT AVG(Gas_Vol)
FROM 3Mon_Average_2 D
WHERE A.Gas_Vol = D.Gas_Vol AND A.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Gas_Vol, (SELECT AVG(Water_Cut)
FROM 3Mon_Average_2 E
WHERE A.Water_Cut = E.Water_Cut AND A.Test_Date BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Cut
FROM 3Mon_Average_2 AS A
ORDER BY A.Test_Date;

 

[dhookom]

Try something like:

SELECT A.Well_Name, A.Test_Date, A.Oil_Vol, A.Water_Vol, A.Gas_Vol,
 A.Water_Cut, DateAdd("m",-3,[Test_Date]) AS SinceDate,
 (SELECT AVG(Oil_Vol) FROM 3Mon_Average_2 B
   WHERE A.Oil_Vol = B.Oil_Vol AND  B.Test_Date
   BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Oil_Vol,  
 (SELECT AVG(Water_Vol) FROM 3Mon_Average_2 C
   WHERE A.Water_Vol = C.Water_Vol AND  C.Test_Date
   BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Vol, 
 (SELECT AVG(Gas_Vol) FROM 3Mon_Average_2 D
   WHERE A.Gas_Vol = D.Gas_Vol AND  D.Test_Date
   BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Gas_Vol, 
 (SELECT AVG(Water_Cut) FROM 3Mon_Average_2 E
   WHERE A.Water_Cut = E.Water_Cut AND  E.Test_Date
   BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Cut
FROM 3Mon_Average_2 AS A
ORDER BY A.Test_Date;

Duane
Hook'D on Access
MS Access MVP

 

[Drivium]

Getting same results as my attempt. 3month oil_vol and standard oil_vol match values.

 

[dhookom]

I think you need to match on the WellName.

SELECT A.Well_Name, A.Test_Date, A.Oil_Vol, A.Water_Vol, A.Gas_Vol,
 A.Water_Cut, DateAdd("m",-3,[Test_Date]) AS SinceDate,
 (SELECT AVG(Oil_Vol) FROM 3Mon_Average_2 B
   WHERE A.Well_Name = B.Well_Name AND  B.Test_Date
   BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Oil_Vol,  
 (SELECT AVG(Water_Vol) FROM 3Mon_Average_2 C
   WHERE A.Well_Name = C.Well_Name AND  C.Test_Date
   BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Vol, 
 (SELECT AVG(Gas_Vol) FROM 3Mon_Average_2 D
   WHERE A.Well_Name = D.Well_Name AND  D.Test_Date
   BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Gas_Vol, 
 (SELECT AVG(Water_Cut) FROM 3Mon_Average_2 E
   WHERE A.Well_Name = E.Well_Name AND  E.Test_Date
   BETWEEN DateAdd("M",-3,A.Test_Date) and A.Test_Date) AS 3Mon_Avg_Water_Cut
FROM 3Mon_Average_2 AS A
ORDER BY A.Test_Date;

Duane
Hook'D on Access
MS Access MVP