[AccessD] Query trouble after adding criteria

[Jim Lawrence]

Hi Chester:

When I was having trouble with queries I would break them apart and stack them in groups, sometimes even creating interim temp files.

I also discovered that large queries may have errors in them and actually loose or add records, for what ever reason. Breaking the queries apart also allows you to more easily make chances to the code. I have also noticed that Access is not nearly as reliable, in record count and performance, as MS SQL, for example.

Jim  

----- Original Message -----
From: "Kaup, Chester" Kaup at kindermorgan.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Thursday, February 2, 2017 1:58:53 PM
Subject: [AccessD] Query trouble after adding criteria

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