It may just be getting late in the day because this seems like it should be simple.
I have an employee who terminated 10/9/2020. He shows up in my current period query because it is between the period from 10/3 through the period to of 10/30. I want him to also show up in the next period query which is period from 10/31 thru period to 11/27. What I would like my query to do is add a field that pulls in the 11/27 date if the term date falls between the prior period dates. I have period table with monthly from and to dates. Termination dates aren't always provided in a timely manner.
Term Date Period From Period To NextPeriodTo
10/9/2020 10/3/2020 10/30/2020 11/27/2020