[AccessD] Query not returning desired results

Tue Dec 10 14:37:45 CST 2019

Thanks everyone for your efforts and ideas. I have opted to delete the offending records from the source table of the query since the table is just a table in the middle of the application that gets created each time the app is run.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence
Sent: Tuesday, December 10, 2019 12:29 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]

Hi Chester:

I am late to this whole adventure but in the past when I was doing very large and complex queries, in many cases the system would either return invalid results or actually crash the MSAccess application. I found out, that if the query could not be run at the DB end using Oracle/MS SQL/etc and result subsequently streamed, it could be managed if the query process was done in slices or in a number of passes.

First slice, using a simple query would map-reduce the data into a temporary file or recordset which could be used to map-reduce the data down, yet again and again as necessary. I found that using the same technique that flatbased files systems used worked well. The difference was of course that you are not fighting against huge data but rather very complex queries. 

The downside is that the query process takes a number of steps that requires more programming but on the upside, the validation of the query results is easy as each temporary datafile can be checked, query errors can be easily found and isolated, the size of the data that can processed is far larger and when this method is being used with and against very complex queries and huge data chunks, the process time is faster.

If you have any questions or interest in using the above described method just ask.

HTH
Jim     

----- Original Message -----
From: "Kaup, Chester" <Chester_Kaup at kindermorgan.com>
To: "Access Developers discussion and problem solving" <accessd at databaseadvisors.com>
Sent: Tuesday, December 10, 2019 9:32:07 AM
Subject: Re: [AccessD] Query not returning desired results

Unfortunately that did not fix the issue. 

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bob Walsh
Sent: Tuesday, December 10, 2019 11:02 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]

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.
-- 
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