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, 
       (SELECT MAX(EffectiveDate) FROM RosterHistory AS rh
             WHERE EmployeeName = RosterHistory.EmployeeName 
             AND EffectiveDate < RosterHistory.EffectiveDate) AS 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