[AccessD] Query trouble after adding criteria

[Darryl Collins]

Hi Chester,

When I hit this type of situation I usually break down the query into smaller parts and built it up in stages.  Assuming you have the space it can also help if you write those stages into temp tables and then run the final query of the table.  This usually adds a considerable performance boost.

Actually I usually always break down any large query into steps.  It makes it much easier to debug and often you can use some of those steps for other queries as well.

I do all the grunt work these days using a SQL Server back end, which is more approachable to this sort of technique, but it work will in Access as well, assuming you stay under the 2GB limit.

As a guess I would try.

1: run the "Distinct" Query on ' qry Injection Data for Oil and Gas Production Allocation' including the " <> Well_In_CO2PAT "and save the output.
2: run a 'Distinct" query on ' qry Injectors in Multi Patterns Alloc Factors' and save
3: In step 3, Only now would you then perform the join query - potentially using a single fake key, rather than 3 fields if the dataset is very large.

Regards
Darryl.

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