[AccessD] Calculation in query problem

Mark Simms marksimms at verizon.net
Tue Dec 20 17:13:43 CST 2011


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





More information about the AccessD mailing list