Results 1 to 6 of 6
  1. #1
    Conniek is offline Novice
    Windows 10 Access 2003
    Join Date
    Oct 2018
    Posts
    3

    Last date in a range with repeating ID numbers

    I have a query that isolates my program header records & includes a program ID number and start date.



    I have a second query that isolates the classes within a program and the corresponding dates.

    I am looking to get the last class in a program.

    Because the same program is offered multiple times in a year, the users are recycling program ID's (not an option to change this) - for example, program ID 1234 has a start date of 6/1/18, but there is also a program ID 1234 with a start date of 8/1/18.

    Normally I would use max date in the range for each ID - because they are reusing ID numbers that won't work. I am needing the last class in the range of dates in sequence with the header record. Any suggestions how to best accomplish this?

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Perhaps use TOP N query, review http://allenbrowne.com/subquery-01.html#TopN
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Conniek is offline Novice
    Windows 10 Access 2003
    Join Date
    Oct 2018
    Posts
    3
    Thanks for the suggestion, but don't think that will work for me. Length of program/number of records varies - some could be 2 days, where others 30. We could have an instance where a 2 day program repeats in the same month..

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Why would that prevent TOP N returning the most recent start date record for each program? Provide sample data and expected output.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Conniek is offline Novice
    Windows 10 Access 2003
    Join Date
    Oct 2018
    Posts
    3
    For example, table data and results I am looking to get below:

    Table #1
    Program ID Start Date
    123 1/15/2018
    123 3/10/2018
    555 4/1/2018


    Table #2
    Program ID Class Date
    123 1/15/2018
    123 1/16/2018
    123 1/17/2018
    123 3/10/2018
    123 3/11/2018
    123 3/12/2018
    123 3/13/2018
    123 3/14/2018
    555 4/1/2018
    555 4/2/2018

    Needed Results
    Program ID Start Date (first date from table 1) End Date (last date in the range from table 2)
    123 1/15/2018 1/17/2018
    123 3/10/2018 3/14/2018
    555 4/1/2018 4/2/2018

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Okay, got it - a 'range' is defined when the continuity of class dates breaks for a program. You're right, this is complicated. I don't see any way to handle this with query alone. Likely need a VBA custom function like:

    Code:
    Function GetEndDate(strPID As String, dteStart As Date) As Date
    Dim rs As DAO.Recordset
    Set rs = CurrentDb.OpenRecordset("SELECT ProgramID, ClassDate FROM Table2 WHERE ProgramID='" & strPID & "' ORDER BY ProgramID, ClassDate;")
    Do While Not rs.EOF
        If dteStart = rs!Classdate Then
            Exit Do
        Else
            rs.MoveNext
        End If
    Loop
    Do While Not rs.EOF
        If dteStart = rs!Classdate Then
            GetEndDate = rs!Classdate
            dteStart = dteStart + 1
            rs.MoveNext
        Else
            Exit Do
        End If
    Loop
    End Function
    Code assumes ProgramID is a text field. If it is a number type, remove the apostrophe delimiters from the recordset query. Also assumes multiple sessions for same program ID are not consecutive. Alternative to VBA would be using the length of a program to simply calculate the end date, assuming a program length is consistent.

    Call function from query:
    SELECT Table1.ProgramID, Table1.StartDate, GetEndDate([ProgramID],[StartDate]) AS EndDate FROM Table1;
    Last edited by June7; 11-18-2018 at 04:53 AM.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

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

Similar Threads

  1. Replies: 17
    Last Post: 07-31-2018, 01:23 PM
  2. Replies: 3
    Last Post: 01-26-2016, 01:56 PM
  3. Replies: 1
    Last Post: 11-13-2014, 08:26 AM
  4. Group Consecutive Numbers in a Range
    By ChiSoxFan in forum Queries
    Replies: 1
    Last Post: 03-20-2014, 04:28 PM
  5. Generating Range of numbers
    By frikkie in forum Access
    Replies: 1
    Last Post: 07-27-2013, 10:46 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