[AccessD] 2 Queries different results

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