[AccessD] Query not returning desired results

[Paul Hartland]

This bit looks ok as long as its the correct fields linking, believe me
done that moee than once & sat there wondering why things don't work lol,
then in theory the results of the query should be correct, if I had access
on my laptop at home I would ask if you minded sending me a bit of the data
from each table & try to help more, but without seeing the data in two
tables its a bit hard to see why it may be returning the incorrect results.

INNER JOIN Allocations ON ([tbl 3 Month Running Inj
Data].Well_in_CO2PAT_API = Allocations.ChildPID)
AND ([tbl 3 Month Running Inj Data].Other_Pattern = Allocations.PID)

On Tue, 10 Dec 2019, 14:52 Kaup, Chester, <Chester_Kaup at kindermorgan.com>
wrote:

> Here is the whole query. Thanks for looking at this
>
> 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].Well_in_CO2PAT_API = [tbl 3 Month Running Inj
> Data].Well_in_CO2PAT_API)
> AND ([qry 3 Months Running Injection Data Totals].ProductionMonth = [tbl 3
> Month Running Inj Data].ProductionMonth))
> INNER JOIN Allocations ON ([tbl 3 Month Running Inj
> Data].Well_in_CO2PAT_API = Allocations.ChildPID)
> AND ([tbl 3 Month Running Inj Data].Other_Pattern = Allocations.PID)
> 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
> Paul Hartland via AccessD
> Sent: Tuesday, December 10, 2019 8:45 AM
> To: Access Developers discussion and problem solving
> Cc: Paul Hartland
> Subject: Re: [AccessD] Query not returning desired results
>
> [This email message was received from the Internet and came from outside
> of Kinder Morgan]
>
>
> What is the full query, maybe there is something slightly wrong in the from
> sql as your referencing two tables in the where, may make a little more
> sense to see the full query.
>
> Paul
>
>
>
> On Tue, 10 Dec 2019, 14:21 Kaup, Chester, <Chester_Kaup at kindermorgan.com>
> wrote:
>
> > 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
>