Results 1 to 6 of 6
  1. #1
    theruvath is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    3

    Question Comparing one record with multiple records in same query and verify

    Hi All,

    I am trying to compare ADate to see if it falls between startdate and enddate for same Name and code combination and get the result:

    E.g: The table i have is



    Name Code Adate StartDate EndDate
    A1 10 1/20/2010 1/20/2010 1/20/2010
    A1 10 3/3/2010 1/20/2010 1/20/2010
    A1 20 1/26/2010 1/26/2010 1/26/2010
    A1 20 1/26/2010 2/22/2010 2/23/2010
    A1 20 1/26/2010 3/18/2010 3/18/2010
    A1 20 2/22/2010 1/26/2010 1/26/2010
    A1 20 2/22/2010 2/22/2010 2/23/2010
    A1 20 2/22/2010 3/18/2010 3/18/2010
    A1 20 2/23/2010 1/26/2010 1/26/2010
    B1 20 2/23/2010 2/22/2010 2/23/2010
    B1 20 2/23/2010 3/18/2010 3/18/2010


    I am trying to write query to get :

    Name Code Adate Result
    A1 10 1/20/2010 Yes
    A1 10 3/3/2010 No
    A1 20 1/26/2010 Yes
    A1 20 2/22/2010 Yes
    A1 20 2/23/2010 Yes
    B1 20 2/23/2010 Yes

    A1 10 is No because there are no date ranges matching for this combination. Please let me know if there is an efficient way of getting the result. Thanks.

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    Select * ,'Yes' as result from table where Adate between startDate and EndDate

    make another query for no. Put them both in a union query.

  3. #3
    theruvath is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    3
    Hi ranman,

    Thanks for suggestion. However after doing union I get Yes and No (2 records) for some . Example - from the sample data I shared, A1 20 1/26/2010 gives both yes and no.
    For such records which satisfies yes and no I just want "Yes" .

    A1 20 1/26/2010 Yes
    A1 20 1/26/2010 No

  4. #4
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    then dont use the no query

  5. #5
    theruvath is offline Novice
    Windows 7 64bit Access 2016
    Join Date
    Jan 2017
    Posts
    3
    I want "no" also to identify records which do not have a match.

  6. #6
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    You can use a subquery for this (see here). Change the names as needed. This query will give you the "Yes" records. I have added an autonumber field to the table so that your second query can be a mismatched query which will give you the no's.

    SELECT Table5.tid, Table5.Aname, Table5.Acode, Table5.Adate, Table5.Startdate, Table5.Enddate, "Yes" AS Ayn
    FROM Table5
    WHERE ((((SELECT Top 1 Dupe.Adate
    FROM Table5 AS Dupe WHERE Dupe.Aname=Table5.Aname And Dupe.Acode=Table5.Acode And Table5.Adate Between Dupe.Startdate and Dupe.Enddate))<>False));

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 04-11-2016, 03:50 PM
  2. Replies: 6
    Last Post: 08-06-2015, 09:44 AM
  3. Replies: 2
    Last Post: 12-04-2013, 03:49 PM
  4. Comparing pairs of records in a query
    By lydonw in forum Queries
    Replies: 2
    Last Post: 08-17-2013, 12:30 AM
  5. Replies: 2
    Last Post: 07-18-2011, 07:27 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums