[AccessD] Query not returning desired results

Tue Dec 10 11:16:55 CST 2019

I'd like to suggest 3 things:

1) Avoid spaces or in table names and query names.  You'll be chasing errors associated with those forever.
2) Avoid underlines in field names.  Again - a forever problem.
3) When you have a complex query like this use the query designer and build it a piece at a time, running the query each time you've added something.  The error messages are cryptic but still helpful. Once the query is working you can view the query in SQL view and copy it to a code window if needed.

Good Luck!

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

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

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