2

I have a table in which I enter attendance of every employee including annual leave (code: LAn) or casual leave (code: LCa).

+-------+-----------+-----+
|  ID   |   Date    | Att |
+-------+-----------+-----+
|  9999 | 01-Jul-19 | LCa |
|  9999 | 02-Jul-19 | LCa |
|  9999 | 03-Jul-19 | LCa |
| 10000 | 15-Jul-19 | LAn |
| 10000 | 16-Jul-19 | LAn |
| 10000 | 17-Jul-19 | LAn |
| 10000 | 18-Jul-19 | LAn |
| 10000 | 19-Jul-19 | LAn |
|  9999 | 22-Jul-19 | LCa |
|  9999 | 23-Jul-19 | LCa |
|  9999 | 24-Jul-19 | LCa |
+-------+-----------+-----+

I am working in MS Access 2016.

It is very similar to this problem: How do I group on continuous ranges, but I want a solution in MS Access. Can anyone help me out please?

I want the query to partition data based on dates so that the output looks something like this:

+-------+-----------+-----------+------+-----+
|  ID   | DateFrom  |  DateTo   | Days | Att |
+-------+-----------+-----------+------+-----+
|  9999 | 01-Jul-19 | 03-Jul-19 |    3 | LCa |
| 10000 | 15-Jul-19 | 19-Jul-19 |    5 | LAn |
|  9999 | 22-Jul-19 | 24-Jul-19 |    3 | LCa |
+-------+-----------+-----------+------+-----+
Lee Mac
  • 15,615
  • 6
  • 32
  • 80
Jahanzeb Khan
  • 33
  • 2

1 Answers1

3

In MS Access, you can use a correlated subquery to generate a sequential number. Then subtract the sequential number from the date to identify the groups:

select id, min(date), max(date), att
from (select t.*,
             (select count(*)
              from t as t2
              where t2.id = t.id and
                    t2.date <= t.date
             ) as seqnum
      from t
     ) as t
group by id, att, dateadd("d", - seqnum, date)
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786