Results 1 to 4 of 4
  1. #1
    soldat452002 is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2014

    Overlapping dates


    So currently im working in migrating data from two Regions (East, West) same data fields but the date ranges (Eff, Exp) either could overlap or not. Im trying to build a query to identify overlapping dates and insert them in order (Oldest to Newest date). Any help would be appreciated it.

    My current query.

    • [East].[Eff] >= [West].[Exp]
    • [East].[Exp] <= [West].[eff]
    • [East].[Affiliation_ID] = [West].[affiliation_ID]
    • [East].[ID] = [West].[ID]

    Id West.Eff West.Exp West.Code West.Affiliation_ID East.Eff East.Exp East.Code East.Affiliation_ID
    19267717 3/4/2020 1/1/2200 TMUTUAL 2971515 10/17/2019 3/3/2020 COMPLETE 2971515
    19267717 3/4/2020 1/1/2200 TMUTUAL 2971515 3/4/2020 1/1/2200 TMUTUAL 2971515
    19313290 12/6/2019 1/1/2200 COMPLETE 2971515 11/27/2019 1/12/2020 COMPLETE 2971515
    19313290 12/6/2019 1/1/2200 COMPLETE 2971515 1/13/2020 1/1/2200 TMUTUAL 2971515

  2. #2
    orange's Avatar
    orange is offline Moderator
    Windows 10 Access 2016
    Join Date
    Sep 2009
    Ottawa, Ontario, Canada; West Palm Beach FL
    Not sure what you're expecting, but this little graphic from P Baldy is often referenced.

  3. #3
    Bullschmidt's Avatar
    Bullschmidt is offline Freelance DB Developer
    Windows 10 Office 365
    Join Date
    Mar 2020
    I am assuming the Eff means effective date and is the starting date and that Exp means the expiration date and is the ending date.

    If so, it looks like you have the criteria already set up well like this:
    [East].[Eff] <= [West].[Exp]
            [East].[Exp] >= [West].[Eff]
        [East].[Affiliation_ID] = [West].[affiliation_ID]
        [East].[ID] = [West].[ID]
    But it looks like the data you show on the report has the first two columns switched in that the supposedly earlier [West].[Eff] data is being shown in the West.Exp column and the supposedly later [West].[Exp] data is being shown in the West.Eff column.

    And for sorting on the earliest start date of two overlapping date ranges perhaps you could create a field something like this to sort on:
    EarliestEffDate: IIf([East].[Eff]<=[West].[Eff],[East].[Eff],[West].[Eff])

  4. #4
    Micron is online now Virtually Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Ontario, Canada
    - "doesn't work" doesn't help. Implement changes in copies of your database.
    Iím telling everyone it's good to eat dried grapes. Itís all about raisin awareness.

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

Similar Threads

  1. find out overlapping dates
    By HS_1 in forum Programming
    Replies: 3
    Last Post: 07-27-2018, 11:48 AM
  2. Overlapping Dates causing duplicates in query
    By AishlinnAnne in forum Database Design
    Replies: 3
    Last Post: 08-31-2016, 06:09 PM
  3. Overlapping Fields
    By pharrison74 in forum Reports
    Replies: 20
    Last Post: 03-02-2016, 09:47 AM
  4. Overlapping Dates in Totals?
    By aellistechsupport in forum Queries
    Replies: 3
    Last Post: 02-01-2016, 08:03 PM
  5. How to query for overlapping dates
    By DavidZ in forum Queries
    Replies: 7
    Last Post: 03-10-2015, 01:54 PM

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 - Senior Forums