[AccessD] Query not returning monthly average

Perfect. Thank You.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Friday, August 24, 2012 12:32 PM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Query not returning monthly average

Hi Chester

Adjust to group by:

Format([WellTest.TestDate],"yyyy-mm")

not the date.

/gustav


>>> Chester_Kaup at kindermorgan.com 24-08-12 19:05 >>>
His query is not returning a monthly average but rather a record for each day. I have used the format method before to get a monthly average. Guess I am missing something here.

SELECT Patt_Members.PID, Patt_Members.ChildPID, Comp_Members.ChildPID, Format([WellTest.TestDate],"yyyy-mm") as MonthDate, WellTest.Well_Name,  Avg(WellTest.LinePres) as LPAvg, Avg(WellTest.TubingPres) as TPAvg FROM dbo_DSS_WellTests AS WellTest, Configmaster AS Comp_Members, Configmaster AS Patt_Members WHERE WellTest.pid = Comp_Members.ChildPID and WellTest.TestDate>#1/1/2012# and Comp_Members.ChildPTYPE = 'COMP'
and comp_Members.PID = Patt_Members.ChildPID and Patt_Members.ChildPTYPE = 'PATT'
and Patt_Members.PID = 'MGRP125'
and (WellTest.Test_Wtri >0 or WellTest.Test_Co2I_MCF>0) Group by Patt_Members.PID, Patt_Members.ChildPID, Comp_Members.ChildPID, WellTest.TestDate, WellTest.Well_Name;


--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com