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.