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