David McAfee
davidmcafee at gmail.com
Wed Mar 10 16:57:42 CST 2010
The only other difference that I can see is in the INNER JOIN:
first query:
INNER JOIN [qry Prod Ctr Patt to Chase Wtr Alloc & Chase Wtr RecordDate] B
ON (A.Other_Pattern_as_Well = B.Other_Pattern_as_Well)
AND (A.Well_in_CO2PAT = B.Well_in_CO2PAT)
second query:
INNER JOIN [qry Prod Ctr Patt to Chase Wtr Alloc & Chase Wtr ProductionMonth] B
ON (A.Other_Pattern_as_Well = B.Other_Pattern_as_Well)
AND (A.Well_in_CO2PAT_API = B.Well_in_CO2PAT_API)
The names of the queries that are joined are different (I used an
alias to make it easier to read) .
The second part of the join also joins on two different named fields
(the 2nd join field names end with "_API"
If those differences don't matter, I'd run the joined queries listed
above with the criteria from this query and see if your results are
identical.
If so, I'd also query the tables and see if the results are identical.
David McAfee
On Wed, Mar 10, 2010 at 2:02 PM, Kaup, Chester
<Chester_Kaup at kindermorgan.com> wrote:
> RecordDate and ProductionMonth contain the same data.
> Something I forgot to mention.
> Query 1 is from an Access 2003 database and Query 2 is from an Access 2007 database. The access 2007 database is basically a copy of the Access 2003 database.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Hewson
> Sent: Wednesday, March 10, 2010 2:49 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] 2 Queries different results
>
> Should it make a difference that in query 1 you are using RecordDate and in
> query 2, ProductionMonth is being used.
> Not sure if this makes a difference or not, but the last field name of each
> result set are different.
> Jim
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
> Sent: Wednesday, March 10, 2010 2:35 PM
> To: Access Developers discussion and problem solving
> Subject: [AccessD] 2 Queries different results
>
> I have two queries in 2 different databases that use the same source data
> but return different results. I am failing to see the difference. Here is
> the data for the first query.
>
> PID SDate Chase_Start_Date SDate_2
> Chase_Start_Date_2
> PAT 140-5 01-Dec-03 01-Mar-07 01-Feb-08
> 01-Aug-09
>
> Here is the data for the second query
>
> PID SDate Chase_Start_Date SDate_2
> Chase_Start_Date2
> PAT 140-5 01-Dec-03 01-Mar-07 01-Feb-08
> 01-Aug-09
>
>
> Here is the first query:
>
> SELECT DISTINCT [tbl Injection Data for Allocation].Well_in_CO2PAT,
> [tbl Injection Data for Allocation].RecordDate,
> [tbl Injection Data for Allocation].Other_Pattern_as_Well,
> IIf([RecordDate]<=[Chase_Start_Date] Or
> [Chase_Start_Date] Is Null Or
> [RecordDate]>=[SDate_2] And [RecordDate]<[Chase_Start_Date_2],1,
> IIf([RecordDate]>[Chase_Start_Date] And
> [RecordDate]<Nz([SDate_2],#1/1/2100#),
> DateDiff("m",[Chase_Start_Date],[RecordDate]),
> IIf([RecordDate]>[Chase_Start_Date_2],
> DateDiff("m",[Chase_Start_Date_2],
> [RecordDate]),1)))
> AS [Months on Chase Water]
> FROM [tbl Injection Data for Allocation]
> INNER JOIN [qry Prod Ctr Patt to Chase Wtr Alloc & Chase Wtr RecordDate]
> ON ([tbl Injection Data for Allocation].Other_Pattern_as_Well =
> [qry Prod Ctr Patt to Chase Wtr Alloc & Chase Wtr
> RecordDate].Other_Pattern_as_Well)
> AND ([tbl Injection Data for Allocation].Well_in_CO2PAT =
> [qry Prod Ctr Patt to Chase Wtr Alloc & Chase Wtr
> RecordDate].Well_in_CO2PAT)
> WHERE ((([tbl Injection Data for Allocation].Well_in_CO2PAT)="139-3")
> AND (([tbl Injection Data for Allocation].RecordDate)=#1/1/2010#)
> AND (([tbl Injection Data for Allocation].Other_Pattern_as_Well)="140-5"))
> ORDER BY [tbl Injection Data for Allocation].RecordDate,
> [tbl Injection Data for Allocation].Other_Pattern_as_Well;
>
> Query Result. This is the correct answer:
> Well_in_CO2PAT RecordDate Other_Pattern_as_Well Months on Chase
> Water
> 139-3 1/1/2010 140-5
> 5
>
> Here is the second query:
> SELECT DISTINCT [tbl Injection Data for Allocation].Well_in_CO2PAT,
> [tbl Injection Data for Allocation].ProductionMonth,
> [tbl Injection Data for Allocation].Other_Pattern_as_Well,
> IIf([ProductionMonth]<=[Chase_Start_Date]Or
> [Chase_Start_Date] Is Null Or
> [ProductionMonth]>=[SDate_2]And [ProductionMonth]<[Chase_Start_Date2],1,
> IIf([ProductionMonth]>[Chase_Start_Date] And
> [ProductionMonth]<Nz([SDate_2],#1/1/2100#),
> DateDiff("m",[Chase_Start_Date],[ProductionMonth]),
> IIf([ProductionMonth]>[Chase_Start_Date2],
> DateDiff("m",[Chase_Start_Date2],
> [ProductionMonth]),1)))
> AS [Months on Chase Water]
> FROM [tbl Injection Data for Allocation]
> INNER JOIN [qry Prod Ctr Patt to Chase Wtr Alloc & Chase Wtr
> ProductionMonth]
> ON ([tbl Injection Data for Allocation].Other_Pattern_as_Well =
> [qry Prod Ctr Patt to Chase Wtr Alloc & Chase Wtr
> ProductionMonth].Other_Pattern_as_Well)
> AND ([tbl Injection Data for Allocation].Well_in_CO2PAT_API =
> [qry Prod Ctr Patt to Chase Wtr Alloc & Chase Wtr
> ProductionMonth].Well_in_CO2PAT_API)
> WHERE ((([tbl Injection Data for Allocation].Well_in_CO2PAT)="139-3")
> AND (([tbl Injection Data for Allocation].ProductionMonth)=#1/1/2010#)
> AND (([tbl Injection Data for Allocation].Other_Pattern_as_Well)="140-5"))
> ORDER BY [tbl Injection Data for Allocation].ProductionMonth,
> [tbl Injection Data for Allocation].Other_Pattern_as_Well;
>
> Query Result. Wrong answer
> Well_in_CO2PAT ProductionMonth Other_Pattern_as_Well Months on Chase
> Water
> 139-3 1/1/2010 140-5
> 1
> 139-3 1/1/2010 140-5
> 5
> 139-3 1/1/2010 140-5
> 21
>
> The query appears to be returning months on chase water value 1 as if it
> never had an SDate, the months between SDate2 and January 2010 and the
> months between Chase_Start_Date2 and January 2010.
>
> Thanks for any help.
>
>
>
>
> Chester Kaup
> Engineering Technician
> Kinder Morgan CO2 Company, LLP
> Office (432) 688-3797
> FAX (432) 688-3799
>
>
> No trees were killed in the sending of this message. However a large number
> of electrons were terribly inconvenienced.
>
>
> --
> 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
>