[AccessD] FW: Query not using criteria

[Kaup, Chester]

Thank You.

-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Monday, June 01, 2015 1:30 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] FW: Query not using criteria

You are joining everything on PID not ChildPID, therefore if there are more than one ChildPID per those mentioned PIDs, you will of course have complementary records in which they pass the <> test. You need to also Outer Join on ChildPID where one side is NULL.

On Thu, May 28, 2015 at 5:14 PM, Kaup, Chester < Chester_Kaup at kindermorgan.com> wrote:

>
> I have a query that does not appear to be using the criteria. Below is 
> the SQL for the query. Certain ChildPID's should be excluded but they 
> are not being excluded. What am I missing? Thanks.
>
> SELECT ConfigMaster.PID, Constants.SDate, ConfigMaster.ChildPID, 
> ConfigMaster.WellName FROM ([tbl Injectors in All Patterns and Factor] 
> INNER JOIN ConfigMaster ON [tbl Injectors in All Patterns and 
> Factor].PID = ConfigMaster.PID) INNER JOIN Constants ON 
> ConfigMaster.PID = Constants.PID WHERE (((ConfigMaster.ChildPID)<>[tbl 
> Injectors in All Patterns and
> Factor]![ChildPID]))
> GROUP BY ConfigMaster.PID, Constants.SDate, ConfigMaster.ChildPID, 
> ConfigMaster.WellName HAVING (((ConfigMaster.PID)="PAT 109-1A"));
>
> This is what should be excluded
>
> PID                                         ChildPID
>  Well_Name        Sdate                    Factor1                 Factor2
> PAT 109-1A         42415034110000               109-1
>  8/1/2006              1                              1
> PAT 109-1A         42415046190000               156-1
>  8/1/2006              .25                          .25
> PAT 109-1A         42415307260000               109-7
>  8/1/2006              .333333                 .333333
> PAT 109-1A         42415342080000               109-1A
> 8/1/2006              1                              1
>
> And here are the query results
>
> PID                         SDate
> ChildPID                               WellName
> PAT 109-1A         8/1/2006                              42415017570000
>            111-2
> PAT 109-1A         8/1/2006                              42415034110000
>            109-1
> PAT 109-1A         8/1/2006                              42415034130000
>            109-3
> PAT 109-1A         8/1/2006                              42415046190000
>            156-1
> PAT 109-1A         8/1/2006                              42415307260000
>            109-7
> PAT 109-1A         8/1/2006                              42415341260000
>            111-7
> PAT 109-1A         8/1/2006                              42415341770000
>            156-1A
> PAT 109-1A         8/1/2006                              42415341880000
>            109-3A
> PAT 109-1A         8/1/2006                              42415342080000
>            109-1A
> --
> 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