[AccessD] Criteria Help in Query

Have you tried a HAVING clause for the conditions instead of adding it to
the WHERE?

Charlotte

On Thu, Dec 20, 2012 at 6:58 AM, Kaup, Chester <
Chester_Kaup at kindermorgan.com> wrote:

> I have the following query that works fine. I need to return only records
> where Test_Oil >0 or Test_HCGas>0 or Test_Wtrp >0. When I add these
> criteria to the query it runs and runs but never completes. Is there a
> better way to do this? 2 queries maybe?
>
> Original query
>
> INSERT INTO Well_Test_Production ( Asset_Id, UWI, [Date], Oil, GasP, WtrP )
> SELECT DISTINCT dbo_DSS_WellTests.PID, dbo_DSS_WellTests.Well_Name,
> dbo_DSS_WellTests.TestDate, dbo_DSS_WellTests.Test_Oil,
> dbo_DSS_WellTests.Test_HCGas, dbo_DSS_WellTests.Test_Wtrp
> FROM dbo_DSS_WellTests, Allocation_Stats,
> (SELECT dbo_DSS_WellTests.PID, Max(dbo_DSS_WellTests.TestDate) AS
> MaxOfTestDate
> FROM Allocation_Stats, dbo_DSS_WellTests
> WHERE
> (((dbo_DSS_WellTests.TestDate)<CDate([Allocation_Stats]![DateFirst_S])))
> GROUP BY dbo_DSS_WellTests.PID)  AS T2
> WHERE (((dbo_DSS_WellTests.PID)=[T2].[PID]) AND
> ((dbo_DSS_WellTests.Testdate)=[MaxofTestDate]))
> ORDER BY dbo_DSS_WellTests.Well_Name;
>
> Query with added criteria
> INSERT INTO Well_Test_Production ( Asset_Id, UWI, [Date], Oil, GasP, WtrP )
> SELECT DISTINCT dbo_DSS_WellTests.PID, dbo_DSS_WellTests.Well_Name,
> dbo_DSS_WellTests.TestDate, dbo_DSS_WellTests.Test_Oil,
> dbo_DSS_WellTests.Test_HCGas, dbo_DSS_WellTests.Test_Wtrp
> FROM dbo_DSS_WellTests, Allocation_Stats,
> (SELECT dbo_DSS_WellTests.PID, Max(dbo_DSS_WellTests.TestDate) AS
> MaxOfTestDate
> FROM Allocation_Stats, dbo_DSS_WellTests
> WHERE
> (((dbo_DSS_WellTests.TestDate)<CDate([Allocation_Stats]![DateFirst_S])))
> GROUP BY dbo_DSS_WellTests.PID)  AS T2
> WHERE (((dbo_DSS_WellTests.PID)=[T2].[PID]) AND
> ((dbo_DSS_WellTests.Testdate)=[MaxofTestDate])
> AND ((dbo_DSS_WellTests.Test_Oil)>0)) OR
> (((dbo_DSS_WellTests.Test_HCGas)>0)) OR (((dbo_DSS_WellTests.Test_Wtrp)>0))
> ORDER BY dbo_DSS_WellTests.Well_Name;
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>