Results 1 to 5 of 5
  1. #1
    EricMorgan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    4

    Develop a query in MS Access 2010 to join two tables using three joins one of which is a date range

    I am trying to develop a query in MS Access 2010 to join two tables using three joins one of which is a (between) date range. The tables are contained in Access.

    ABCPART links to XYZPART. ABCSERIAL links to XYZSERIAL. ABCDATE links to (between) XYZDATE1 and ZYZDATE2.



    [ABCTABLE]
    ABCORDER
    ABCPART
    ABCSERIAL
    ABCDATE

    [ZYXTABLE]
    XYZORDER
    XYZPART
    XYZSERIAL
    XYZDATE1
    XYZDATE2

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    You wouldn't join on the date,
    you'd filter each table on the date range but join on keys.

  3. #3
    EricMorgan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    4
    In the criteria, how would I do between dates. The following doesn't work. Sorry I am a noob.
    Between([XYZTABLE]![XYZDATE1] and [XYZTABLE]![XYZDATE2])

  4. #4
    EricMorgan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    4
    Sorry I am bad at explaining things. Let me try that again... This time I am going to use the actual table/column names.

    This table has many orders (MSORDER) that need to match one order (GLORDER) in GLORDR. This is based on MSPART linked to GLPART, MSSERIAL linked to GLSERIAL, and MSOPNDATE falls between GLSTARTDATE and GLENDDATE.
    [MESORDR]
    MSORDER MSPART MSSERIAL MSOPNDATE
    11111111 4444444 55555 2/4/2015
    22222222 6666666 11111 1/6/2015
    33333333 6666666 11111 3/5/2015

    This table has one order for every part number and every serial number.
    [GLORDR]
    GLORDER GLPART GLSERIAL GLSTARTDATE GLENDDATE
    ABC11111 444444 55555 1/2/2015 4/4/2015
    ABC22222 666666 11111 1/5/2015 4/10/2015
    AAA11111 555555 22222 3/2/2015 4/10/2015

    Query table
    [GLORDR] [MESORDR] [GLORDR] [GLORDR] [MESORDR]
    GLORDER MSORDER GLSTARTDATE GLENDDATE MSOPNDATE
    ABC11111 11111111 1/2/2015 4/4/2015 2/4/2015
    ABC22222 22222222 1/5/2015 4/10/2015 1/6/2015
    ABC22222 33333333 1/5/2015 4/10/2015 3/5/2015

  5. #5
    EricMorgan is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Apr 2015
    Posts
    4
    I figured it out.

    SELECT GLORDR.GLORDER, MSORDR.MSORDER, GLORDR.GLSTARTDATE, GLORDR.GLENDDATE, MSORDR.MSOPNDATE
    FROM GLORDR INNER JOIN MSORDR ON (GLORDR.GLPART = MSORDR.MSPART) AND (GLORDR.GLSERIAL = MSORDR.MSSERIAL)
    WHERE (((MSORDR.MSOPNDATE) Between [GLORDR].[GLSTARTDATE] And [GLORDR].[GLENDDATE]));

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

Similar Threads

  1. Develop in Access 2013 run in Access 2010
    By Sck in forum Programming
    Replies: 3
    Last Post: 01-27-2015, 03:23 PM
  2. Replies: 2
    Last Post: 08-07-2014, 01:08 PM
  3. Replies: 11
    Last Post: 11-08-2013, 06:14 PM
  4. joins based on date range
    By broecher in forum Database Design
    Replies: 4
    Last Post: 11-04-2011, 06:32 PM
  5. Daily reports from date range tables?
    By sparker in forum Database Design
    Replies: 0
    Last Post: 03-29-2010, 11:07 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