is there any way to do this with sql queries in ms access?

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.

Leave a Comment

Your email address will not be published.

Scroll to Top