[AccessD] Query trouble after adding criteria

[Jim Dettman]

 Drop to a command prompt.

 Type SET and a return. 

 Confirm that you have a TEMP and TMP environment variable and that they
point to a valid drive/directory with free disk space.

 If you do, then your stuck breaking it down as the others have said.
JET/ACE uses these to find where it should create a temp db (~JETxxxx.tmp).

 Keep in mind that HAVING is applied last, so it needs to build the entire
thing up first, then check if it should keep the rows or not.  So it's
possible that bumps the 2GB container limit.

Jim. 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Kaup, Chester
Sent: Thursday, February 02, 2017 04:59 PM
To: Access Developers discussion and problem solving
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