[AccessD] Need Query Help

Chester,

    Considering further clarifications from your end, the sample query has been revised suitably as given below. You might like to actually try it out over diverse type of data 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 ((SDate_2 Is Null And Chase_Start_Date Is Null) Or (SDate_2 Is Null And Chase_Start_Date >= #1/1/2005#) Or (SDate_2 < #1/1/2005# And (Chase_Start_Date Is Null Or Chase_Start_Date < SDate_2 Or Chase_Start_Date >= #1/1/2005#)));
=====================================

  ----- Original Message ----- 
  From: Kaup, Chester 
  To: Access Developers discussion and problem solving 
  Sent: Thursday, October 22, 2009 18:07
  Subject: Re: [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