I believe this gives something close to what you’ve asked for. (Whether it’s what you want or need may be a different matter as there are assumptions built into your question, such as that all your dates fall within a given month and hence if there is no prior record then the days are counted from the beginning of the month).
SELECT EmployeeName, Previous_Supervisor, Updated_Supervisor, ChangeType, Updated_Status, EffectiveDate, (SELECT MAX(EffectiveDate) FROM RosterHistory AS rh WHERE EmployeeName = RosterHistory.EmployeeName AND EffectiveDate < RosterHistory.EffectiveDate) AS DatePrevious, IIF(ISNULL(DatePrevious), DAY(EffectiveDate) - 1, EffectiveDate - DatePrevious) AS ActiveDayCount FROM RosterHistory;
Note: Although it looks innocuous, the “AS rh” in
RosterHistory AS rh above is critically important. Without that the query will run but will give incorrect output/no values for the DatePrevious column.
Just to note, I have tested this in MS Access 2016 with a copy of the sample data provided.
CLICK HERE to find out more related problems solutions.