Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Nov 2004
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Access comparison query (2003)

    Hi,

    I'm trying to compare a number field called servdate in table called tblSTABLE to another table called tblYMD that contains two fields called YMDEFF and YMDEND and show a return on one record with one field called LOB in a QUERY. The servdate field should fall between the range of the fields YMDEFF and YMDEND.

    Please help!!!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access comparison query (2003)

    I'm not sure what you want. Here is the SQL for a possible query:

    SELECT tblSTABLE.MEMBER_NUM, tblSTABLE.LOB, tblSTABLE.servdate
    FROM tblYMD INNER JOIN tblSTABLE ON (tblYMD.LOB = tblSTABLE.LOB) AND (tblYMD.MEMBER_NUM = tblSTABLE.MEMBER_NUM)
    WHERE (((tblSTABLE.servdate) Between [YMDEFF] And [YMDEND]));

    It returns multiple records for a MEMBER_NUM/LOB combination (with different dates). If you want one record per MEMBER_NUM/LOB combination:

    SELECT DISTINCT tblSTABLE.MEMBER_NUM, tblSTABLE.LOB
    FROM tblYMD INNER JOIN tblSTABLE ON (tblYMD.LOB = tblSTABLE.LOB) AND (tblYMD.MEMBER_NUM = tblSTABLE.MEMBER_NUM)
    WHERE (((tblSTABLE.servdate) Between [YMDEFF] And [YMDEND]));

    And if you want to return only LOB:

    SELECT DISTINCT tblSTABLE.LOB
    FROM tblYMD INNER JOIN tblSTABLE ON (tblYMD.LOB = tblSTABLE.LOB) AND (tblYMD.MEMBER_NUM = tblSTABLE.MEMBER_NUM)
    WHERE (((tblSTABLE.servdate) Between [YMDEFF] And [YMDEND]));

  3. #3
    New Lounger
    Join Date
    Nov 2004
    Posts
    20
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Access comparison query (2003)

    Thank you it worked!!! But I still have a little problem, from the SQL statements you provided, how can I get those servdate that are not between the YMEFF and YMDEND. Thank you so MUCH your assistance.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Access comparison query (2003)

    You can change the criteria to

    WHERE (((tblSTABLE.servdate) Not Between [YMDEFF] And [YMDEND]))

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •