[AccessD] Need Query Help

I don't believe statement a) will work correctly because it would include the following record that should not be counted if I understand your logic correctly

PID          SDate     Chase_Start_Date     SDate_2
PAT 118-5  01-Jan-96     01-Oct-02


Basically SDate is the start date, Chase_Start_Date is the turned off date and SDate_2 is the restart date. What am trying to get to is a list of the PID's that are active as of 12/31/2004.

Thanks for your assistance.


-----Original Message-----
From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of A.D.Tejpal
Sent: Thursday, October 22, 2009 12:47 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Need Query Help

Chester,

    Apparently, the criteria could be rephrased as follows:
    (a) Include all records where SDate < #1/1/2005# and SDate_2 is Null or < #1/1/2005#, irrespective of the value held by Chase_Start_Date.
    (b) Also include records where  SDate < #1/1/2005#  and Chase_Start_Date is Null or >= #1/1/2005#

    You might like to try out the sample query given below over a larger data set and verify whether the results are in line with those sought by you.

Best wishes,
A.D. Tejpal
------------

Sample Query
===============================
SELECT Constants.*  
FROM Constants  
WHERE (SDate < #1/1/2005# And Nz(SDate_2, #1/1/2005#) < #1/1/2005#) Or (SDate < #1/1/2005# And Nz(Chase_Start_Date, #1/1/2005#) >= #1/1/2005#);
===============================

  ----- Original Message ----- 
  From: Kaup, Chester 
  To: Access Developers discussion and problem solving 
  Sent: Wednesday, October 21, 2009 19:23
  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