[AccessD] Query trouble after adding criteria

[Kaup, Chester]

Thanks everyone for your ideas. Ended up using the original query that generated all the records and then running a delete query to remove the ones not needed. Nothing else seemed to want to work.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Friday, February 03, 2017 6:48 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query trouble after adding criteria

[This email message was received from the Internet and came from outside of Kinder Morgan]


Not sure why it is dong it,

but have you tried replacing the Having Clause with a Where Clause before the Group by?


On 2 Feb 2017 at 21:58, Kaup, Chester wrote:

> I have the following query that runs fine and returns 37,736 records.
> 
> SELECT DISTINCT [qry Injection Data for Oil and Gas Production 
> Allocation].Well_In_CO2PAT, [qry Injection Data for Oil and Gas 
> Production Allocation].Well_in_CO2PAT_API, [qry Injection Data for Oil 
> and Gas Production Allocation].ProductionMonth, [qry Injection Data 
> for Oil and Gas Production Allocation].Other_Pattern, [qry Injection 
> Data for Oil and Gas Production Allocation].Other_Pattern_as_Well,
> [qry Injection Data for Oil and Gas Production 
> Allocation].Other_Pattern_as_Well_API, [qry Injection Data for Oil and 
> Gas Production Allocation].VM_WI, [qry Injection Data for Oil and Gas 
> Production Allocation].VM_CI, [qry Injectors in Multi Patterns Alloc 
> Factors].WaterInj, [qry Injectors in Multi Patterns Alloc 
> Factors].CO2Inj FROM [qry Injection Data for Oil and Gas Production 
> Allocation] INNER JOIN [qry Injectors in Multi Patterns Alloc Factors] 
> ON ([qry Injection Data for Oil and Gas Production 
> Allocation].Well_In_CO2PAT = [qry Injectors in Multi Patterns Alloc
> Factors].Well_Name) AND ([qry Injection Data for Oil and Gas 
> Production Allocation].ProductionMonth = [qry Injectors in Multi 
> Patterns Alloc Factors].ProductionMonth) AND ([qry Injection Data for 
> Oil and Gas Production Allocation].Other_Pattern = [qry Injectors in 
> Multi Patterns Alloc Factors].PID) GROUP BY [qry Injection Data for 
> Oil and Gas Production Allocation].Well_In_CO2PAT, [qry Injection Data 
> for Oil and Gas Production Allocation].Well_in_CO2PAT_API, [qry 
> Injection Data for Oil and Gas Production Allocation].ProductionMonth, 
> [qry Injection Data for Oil and Gas Production 
> Allocation].Other_Pattern, [qry Injection Data for Oil and Gas 
> Production Allocation].Other_Pattern_as_Well, [qry Injection Data for 
> Oil and Gas Production Allocation].Other_Pattern_as_Well_API, [qry 
> Injection Data for Oil and Gas Production Allocation].VM_WI, [qry 
> Injection Data for Oil and Gas Production Allocation].VM_CI, [qry 
> Injectors in Multi Patterns Alloc Factors].WaterInj, [qry Injectors in 
> Multi Patterns Alloc Factors].CO2In:
> 
> When I add the following criteria I get an error message The query 
> cannot be completed. Either the size of the query results is larger 
> than the maximum size of (2 GB), or there is not enough temporary 
> space on the disk to store the query result. Looking at the size of 
> the file I see no change. There is 14. GB of free space on the drive.
> The criteria is listed below
> 
> HAVING ((([qry Injection Data for Oil and Gas Production 
> Allocation].Other_Pattern_as_Well)<>[Well_In_CO2PAT]));
> 
> Any ideas would be appreciated.
> 
> --
> 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