[AccessD] Query not returning desired results

Tue Dec 10 11:01:37 CST 2019

Did you try this?

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

[Attn: This is an external email.]

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<http://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_as_Well_API Is Null)
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<http://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<http://www.bchacc.com>
www.e-z-mrp.com<http://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<mailto:AccessD at databaseadvisors.com>
> http://databaseadvisors.com/mailman/listinfo/accessd<http://databaseadvisors.com/mailman/listinfo/accessd>
> Website: http://www.databaseadvisors.com<http://www.databaseadvisors.com>
>

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

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

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

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

________________________________
This message and any attachments are intended for the use of the individual to whom it is addressed and may contain information that is privileged, business sensitive, confidential and/or exempt from disclosure under applicable laws and regulations. If you are not the intended recipient, be aware that any disclosure, copying, distribution, or use of the contents of this transmission is prohibited. If you have received this message in error, please inform the sender and delete all copies. Thank you.