Find Latest Benefit Enrollment

Sort:
You are not authorized to post a reply.
Author
Messages
Heidi
New Member
Posts: 2
New Member

    We are attempting to write a query in Access that will grab the most recent benefit enrollment for an employee.  We are finding that if a status change is performed that affects the benefit for the employee a stop date is placed on the first enrollment with a start date the day following.  (For instance the employee goes from part time to full time which affect their premium amount).  For for 2015, they may have an enrollment from 1/1/15 - 3/14/15 and then a subsequent enrollment starting at 3/15/15.   We want to return the 3/15/15 record in the query.  We also want to report on any enrollments that may have stopped during that time frame so unfortunately we can't just filter out the stopped enrollments.  

    I have been trying all sorts of variations of queries and so far no luck.  Has anyone happened to have developed anything along these lines that they would be willing to share?  Even if this has not been developed in Access it might send me in the right direction on the logic.  

    Any help would be greatly appreciated!  Thanks.  

    The.Sam.Groves
    Veteran Member
    Posts: 89
    Veteran Member
      In SQL you'd do this using a subquery against the same table, selecting the max start date with the same key fields. Then assigning your main query's start date to that value.
      Heidi
      New Member
      Posts: 2
      New Member
        Thank you! This is what I needed to point me in the right direction.
        You are not authorized to post a reply.