[AccessD] Query trouble after adding criteria

[Stuart McLachlan]

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
>