[AccessD] Count Average in Query

Sorry sent wrong copy

You would have to do a subquery or link two queries

Here is the subquery

Select YearMonth,
       count(WellId)  WellOver1000 
       Avg(Sum(PressureAmt) / Sum(Recnt)) AvgOvr
>From (Select Yt1.YearMonth,
             Yt1.WellId,
             Count(Yt1.WellID)  RecCnt,
             Sum(Yt1.CasePressure) PressureAmt,
             AVG(sum(Yt1.CasePressure) / Count(Yt1.WellId)) AvgPressure
      From YourTable Yt1
      HAVING  AVG(sum(Yt1.CasePressure) / Count(Yt1.WellID)) > 1000
      GROUP BY Yt1.YearMonth, Yt1.WellId )
Group by YearMonth


**************************************************
* Patricia O'Connor    
* Information Technology Specialist 3 (Programming) 
* OTDA - BDMA
* (W) mailto:Patricia.O'Connor at otda.state.ny.us
* (w) mailto:aa1160 at nysemail.state.ny.us
**************************************************
 

> 
--------------------------------------------------------
This e-mail, including any attachments, may be confidential, privileged or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy or otherwise use this e-mail or its attachments.  Please notify the sender immediately by reply e-mail and delete the e-mail from your system. 


-----Original Message-----

> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of 
> Kaup, Chester
> Sent: Friday, March 28, 2008 10:48 AM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] Count Average in Query
> 
> I have a table that has casing pressures for hundreds of 
> wells. There can be anywhere from 1 to 31 values per well per 
> month. What I need to extract from the table is a count by 
> month of wells that have an average casing pressure over 1000 
> psi. I am not quite sure how do this best. I could calculate 
> a monthly average for each well, then extract the ones over 
> 1000 and then count them. This just doesn't seem like the 
> best way to go. Thanks.
> 
>  
> 
> Chester Kaup
> 
> Engineering Technician
> 
> Kinder Morgan CO2 Company, LLP
> 
> Office (432) 688-3797
> 
> FAX (432) 688-3799
> 
>  
> 
>  
> 
> No trees were killed in the sending of this message. However 
> a large number of electrons were terribly inconvenienced.
> 
>  
> 
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
>