Results 1 to 6 of 6
  1. #1
    bigalxyz is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    London, UK
    Posts
    3

    Tricky Job with Date Ranges

    Hello forum,



    I've got a tricky little job to do which involves calculating the overlap between certain date ranges. I've written a bit of code in VBA/Excel which works fine, but it's appallingly slow - and I think this sort of thing belongs in Access (or similar) rather than Excel anyway.

    Very brief summary of what needs doing...

    Have a main table (could be millions of rows) which looks something like:

    Main_Table
    =======
    * Account_Number_Index
    * Start_Date
    * End_Date
    * Data_Item_1
    * Data_Item_2
    ...
    ...
    ...
    * Data_Item_N




    I also have a small table of "analysis periods" (typically 4-6 rows only)


    Analysis_Periods
    ===========
    * Period_Index
    * Period_Start_Date
    * Period_End_Date
    * Period_Label



    I need to create an output table from all of this which has one record for each combination of Account_Number_Index and Period_Index. Fields would be:

    Output_Table
    =========
    * Account_Number_Index
    * Overlap_Start_Date (explained below)
    * Overlap_End_Date (explained below)
    * Period_Index
    * Period_Lable
    * Data_Item_1
    * Data_Item_2
    ...
    ...
    ...
    * Data_Item_N




    So in loose pseudo-code it would look something like this:




    For each row in Main_Table

    For each row in Analysis_Periods

    Work out the overlapping period (Overlap_Start_Date...Overlap_End_Date) for time intervals (Start_Date...End_Date) and (Period_Start_Date...Period_End_Date)

    If there is no overlap at all

    Do nothing

    Else

    Output a row of data to Output_Table which is identical to the row in the Main_Table except that:
    * Overlap_Start_Date used instead of Start_Date
    * Overlap_End_Date used instead of End_Date
    * Period_Index and Period_Label tagged also output

    End If

    Next row in Analysis_Periods

    Next row in Main_Table






    Working out the overlapping period is just a case of the time interval between:
    (a) max(Start_Date, Period_Start_Date)
    (b) min(End_Date, Period_End_Date)




    Have attached a scribble on a bit of paper re: overlapping time intervals, if that helps visualise the problem.

    Can anyone suggest an efficient way of doing this in Access please, either with some SQL code or some VBA (or both)?

    I'm not an expert in this stuff so I hope I'm making sense...apologies if not.

    Most grateful for any assistance.

    Thx
    A
    Attached Thumbnails Attached Thumbnails Scan0001.jpg  

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    see post #6 on this thread - similar problem, same principle to resolve

    https://www.accessforums.net/showthread.php?t=74342

  3. #3
    bigalxyz is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    London, UK
    Posts
    3
    Thank you - but I don't see a link to a thread in your reply - have I missed something (not unknown!)?

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry have added it to my post

  5. #5
    bigalxyz is offline Novice
    Windows 10 Access 2016
    Join Date
    Nov 2018
    Location
    London, UK
    Posts
    3
    Thank you. Will read it ASAP.

  6. #6
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Just curious.....

    You gave an example of your main table with a structure of
    Code:
    Main_Table
    =======
    * Account_Number_Index
    * Start_Date
    * End_Date
    * Data_Item_1
    * Data_Item_2
    ...
    ...
    ...
    * Data_Item_N
    What is "* Data_Item_1", "* Data_Item_2",..., "* Data_Item_N"?

    It appears that you are saying the Main Table has the FIELDS above; how many fields are there in total for the Main_Table (and the Output table)?

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

Similar Threads

  1. Consecutive Date Ranges
    By ProwlingCamel in forum Access
    Replies: 6
    Last Post: 09-29-2015, 12:51 AM
  2. displaying date ranges.
    By abodi in forum Queries
    Replies: 4
    Last Post: 08-15-2014, 08:05 AM
  3. Replies: 5
    Last Post: 12-12-2013, 12:14 PM
  4. How to Program Two Date Ranges?
    By Jaynen in forum Database Design
    Replies: 5
    Last Post: 02-22-2013, 06:58 AM
  5. Searching for Date Ranges
    By phd42122 in forum Access
    Replies: 2
    Last Post: 05-07-2012, 07:20 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