[AccessD] Query trouble after adding criteria

[Gustav Brock]

Hi Chester

Try to remove the Distinct and perhaps also leave the criteria.
Then create another query using the first as source where you apply Distinct (or Group By) and the criteria if this was not applied in the first query.

/gustav

-----Oprindelig meddelelse-----
Fra: AccessD [mailto:accessd-bounces at databaseadvisors.com] På vegne af Kaup, Chester
Sendt: 2. februar 2017 22:59
Til: Access Developers discussion and problem solving <accessd at databaseadvisors.com>
Emne: [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.