[AccessD] Criteria Help in Query

Kaup, Chester Chester_Kaup at kindermorgan.com
Thu Dec 20 08:58:38 CST 2012


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;


More information about the AccessD mailing list