[AccessD] Query not returning desired results

[Kaup, Chester]

Here is the whole query without all the parenthesis in the where clause

SELECT [qry 3 Months Running Injection Data Totals].Well_In_CO2PAT, 
[tbl 3 Month Running Inj Data].Well_in_CO2PAT_API, 
[tbl 3 Month Running Inj Data].ProductionMonth, 
[tbl 3 Month Running Inj Data].Other_Pattern, 
[tbl 3 Month Running Inj Data].Other_Pattern_as_Well, 
[tbl 3 Month Running Inj Data].Other_Pattern_as_Well_API, 
[tbl 3 Month Running Inj Data].[3mo_rbi], [
qry 3 Months Running Injection Data Totals].SumOf3mo_rbi, 
IIf([SumOf3mo_rbi]=0,Allocations.Factor_2,[3mo_rbi]/[SumOf3mo_rbi]) AS Factor_2 
INTO [tbl 3 Month Factor 2]
FROM ([qry 3 Months Running Injection Data Totals] 
INNER JOIN [tbl 3 Month Running Inj Data] ON ([qry 3 Months Running Injection Data Totals].ProductionMonth = [tbl 3 Month Running Inj Data].ProductionMonth) 
AND ([qry 3 Months Running Injection Data Totals].Well_in_CO2PAT_API = [tbl 3 Month Running Inj Data].Well_in_CO2PAT_API)) 
INNER JOIN Allocations ON ([tbl 3 Month Running Inj Data].Other_Pattern = Allocations.PID) 
AND ([tbl 3 Month Running Inj Data].Well_in_CO2PAT_API = Allocations.ChildPID)
WHERE [tbl 3 Month Running Inj Data].Other_Pattern<>"PAT 28-5A" 
And [tbl 3 Month Running Inj Data].Other_Pattern<>"PAT 56-6A" 
AND [tbl 3 Month Running Inj Data].Other_Pattern_as_Well_API Is Null 
AND Allocations.Date=#11/1/1948#
GROUP BY [qry 3 Months Running Injection Data Totals].Well_In_CO2PAT, 
[tbl 3 Month Running Inj Data].Well_in_CO2PAT_API, 
[tbl 3 Month Running Inj Data].ProductionMonth, 
[tbl 3 Month Running Inj Data].Other_Pattern, 
[tbl 3 Month Running Inj Data].Other_Pattern_as_Well, 
[tbl 3 Month Running Inj Data].Other_Pattern_as_Well_API, 
[tbl 3 Month Running Inj Data].[3mo_rbi],
[qry 3 Months Running Injection Data Totals].SumOf3mo_rbi, 
IIf([SumOf3mo_rbi]=0,Allocations.Factor_2,[3mo_rbi]/[SumOf3mo_rbi]);

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of rockysmolin bchacc.com
Sent: Tuesday, December 10, 2019 10:17 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query not returning desired results

[This email message was received from the Internet and came from outside of Kinder Morgan]


Can you post again the query without the parentheses - may be easier for one of us to see the problem.


Rocky Smolin
Beach Access Software
760-683-5777
www.bchacc.com
www.e-z-mrp.com
Skype: rocky.smolin
 


-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Tuesday, December 10, 2019 6:21 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Query not returning desired results

Got rid of all the unneeded parentheses but still got the same incorrect result. Thanks for the help.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Monday, December 09, 2019 2:54 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Query not returning desired results

[This email message was received from the Internet and came from outside of Kinder Morgan]


hey are all AND conditions, so for a start, just get rid of all parentheses.


On 9 Dec 2019 at 17:15, Kaup, Chester wrote:

> I have a query in which I want to exclude records when one field
> equals a certain value and another field in the same record is null.
> So far it have been unsuccessful after several attempts of moving
> around parenthesis. Here is my statement. I defer to and appreciate
> the assistance of other members
> 
> WHERE ((([tbl 3 Month Running Inj Data].Other_Pattern)<>"PAT 28-5A"
> And ([tbl 3 Month Running Inj Data].Other_Pattern)<>"PAT 56-6A") AND
> (([tbl 3 Month Running Inj Data].Other_Pattern_as_Well_API) Is Null)
> AND ((Allocations.Date)=#11/1/1948#))
> 
> Also tried this
> 
> WHERE (([tbl 3 Month Running Inj Data].Other_Pattern<>"PAT 28-5A" AND
> [tbl 3 Month Running Inj Data].Other_Pattern<>"PAT 56-6A" AND [tbl 3
> Month Running Inj Data].Other_Pattern_as_Well_API Is Null) AND
> Allocations.Date=#11/1/1948#)
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com