[AccessD] Calculation in query problem

Kaup, Chester Chester_Kaup at kindermorgan.com
Tue Dec 20 16:44:15 CST 2011


Good observation. Thanks.

-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Tuesday, December 20, 2011 3:59 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Calculation in query problem

You must have eleven records that match your SELECT... JOIN.... WHERE...HAVING   
criteria.

Sum() is adding the values in each of those eleven distinct records together.

-- 
Stuart
On 20 Dec 2011 at 12:57, Kaup, Chester wrote:

> I have the following query
> SELECT [tbl Produced Gas Meters].Meter_ID, scada_V_All_Meter_Volumes.ReadingDate, [Volume]*1000 AS Mcf, Sum([Volume])*1000 AS McfTest
> FROM (([tbl Produced Gas Meters] 
> INNER JOIN scada_Gas_Meter_Master ON [tbl Produced Gas Meters].Meter_ID = scada_Gas_Meter_Master.Meter_ID) 
> INNER JOIN ([qry Last Gas Analysis Date] 
> INNER JOIN (GA_Header INNER JOIN GA_Details ON 
> GA_Header.ID = GA_Details.HEADER) ON 
> ([qry Last Gas Analysis Date].MaxOfSample_Date = GA_Header.Sample_Date) 
> AND ([qry Last Gas Analysis Date].METER = GA_Header.METER)) 
> ON [tbl Produced Gas Meters].[Meter_ID Text] = GA_Header.METER) 
> INNER JOIN scada_V_All_Meter_Volumes ON scada_Gas_Meter_Master.Tagname = scada_V_All_Meter_Volumes.Tagname
> WHERE (((GA_Details.UNIT)="PCT"))
> GROUP BY [tbl Produced Gas Meters].Meter_ID, scada_V_All_Meter_Volumes.ReadingDate, [Volume]*1000
> HAVING ((([tbl Produced Gas Meters].Meter_ID)=362915) 
> AND ((scada_V_All_Meter_Volumes.ReadingDate)=#12/1/2011#))
> ORDER BY scada_V_All_Meter_Volumes.ReadingDate;
> 
> The expression [Volume]*1000 AS Mcf returns the value 18259.5043182373
> 
> The expression Sum([Volume])*1000 as McfTest returns 200854.54750061
> 
> I have a feeling I am missing something in the order of calculations as done by Access. 
> 
> Any thoughts appreciated.
> 
> 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
> 




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





More information about the AccessD mailing list