[AccessD] Need Query Help

This works good on the sample data however I forgot to include examples of a couple of other record types that are considered bad.

PID		SDate		Chase_Start_Date		SDate_2
PAT 104-1			
PAT 26-6	4/1/2009

Thanks.		


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jack and Pat
Sent: Wednesday, October 21, 2009 9:21 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] Need Query Help

Chester, 

I think this works:

SELECT Constants.SDate, Constants.Chase_Start_Date, Constants.SDate_2
FROM Constants
WHERE  
          [SDate]<#1/1/2005# 
AND   [SDate_2]<#1/1/2005#
AND   [Chase_Start_date]<#1/1/2005# 
             
OR 
       (   [Chase_Start_Date]  Is Null
 AND  [SDate_2] Is Null);

Works on the test records .

Jack

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Kaup, Chester
Sent: Wednesday, October 21, 2009 9:54 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Need Query Help

What if Chase_Start_Date < 1/1/2005 and SDate_2 is < 1/1/2005?
Count the record if Sdate <1/1/2005

Examples
Good Records
PID		SDate		Chase_Start_Date		SDate_2
PAT 77-2	01-Feb-03		
PAT 117-1	01-May-98	01-Oct-02			01-Feb-04

Bad Record
PAT 118-5	01-Jan-96	01-Oct-02
PAT 173-3	01-Oct-99	01-Apr-04			01-Mar-08


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Wednesday, October 21, 2009 8:27 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Need Query Help

Hi Chester

2. Don't include record with SDate <1/1/2005 if Chase_Start_Date < 1/1/2005
3. Do include record if SDate <1/1/2005 and SDate_2 is < 1/1/2005

are contradicting. What if Chase_Start_Date < 1/1/2005 and SDate_2 is <
1/1/2005?

/gustav


>>> Chester_Kaup at kindermorgan.com 21-10-2009 15:04 >>>
I have a table with 4 fields PID, SDate, Chase_Start_Date and SDate_2.
I need to extract records based on the following criteria.

1. Sdate < 1/1/2005
2. Don't include record with SDate <1/1/2005 if Chase_Start_Date < 1/1/2005
3. Do include record if SDate <1/1/2005 and SDate_2 is < 1/1/2005

The Chase_Start_Date and SDate_2 fields may be blank or just the Sdate_2
field if the Chase_Start_Date field has data.

I have been working on this for a while and thought I had it with the
following SQL statement but no. Time for another set of eyes to look at it.

SELECT SDate, Chase_Start_Date, SDate_2
FROM Constants
WHERE (((SDate)<#1/1/2005#) 
AND ((Chase_Start_Date) Is Null)) 
OR (((SDate)<#1/1/2005#) AND ((SDate_2)<#1/1/2005#));

Thanks.

Chester Kaup
Engineering Technician
Kinder Morgan CO2 Company, LLP
Office (432) 688-3797
FAX (432) 688-3799



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