Kaup, Chester
Chester_Kaup at kindermorgan.com
Wed Dec 28 10:13:39 CST 2011
Thanks for the suggestion on how to write a better query. The extra records were caused by there being 11 records for each gas meter in the table GA_Details. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Mark Simms Sent: Tuesday, December 20, 2011 5:14 PM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Calculation in query problem Dunno...the Where clause was not being utilized properly for one thing. This will run much more efficiently.... I couldn't really pin-point the problem though. 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" ) AND ([tbl Produced Gas Meters].Meter_ID ) = 362915) AND ( scada_V_All_Meter_Volumes.ReadingDate = #12 / 1 / 2011 # ) GROUP BY [tbl Produced Gas Meters].Meter_ID, scada_V_All_Meter_Volumes.ReadingDate, [Volume] * 1000 ORDER BY scada_V_All_Meter_Volumes.ReadingDate; > -----Original Message----- > From: accessd-bounces at databaseadvisors.com [mailto:accessd- > bounces at databaseadvisors.com] On Behalf Of Kaup, Chester > Sent: Tuesday, December 20, 2011 1:58 PM > To: Access Developers discussion and problem solving > Subject: [AccessD] Calculation in query problem > > 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