# Access query to find consecutive numbers

I have a table in Access with two columns: the first is the ID of a person, the second is the day of the week they were present somewhere.  So it looks like this:

ID                          Day
1                            10
1                            11
2                            6
3                            4
3                            5
3                            6
3                            23

What I would like to do is:

1) Return a list of IDs and Days for the consecutive dates only.
1         10
1         11
3          4
3          5
3          6

2) Return a list of IDs and Days for the non-consecutive dates only.
2          6
3          23

How would I do that?
###### Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
"the second is the day of the week" - you have values up to 23. May be it is day of month?
What we should do with minimum and maximum values? February, for example can have 28 or 29 days. How can we know how many days has month? If in your example you will have also record 3 and 22, what is expected result?
0
Author Commented:
Sorry I meant the days in a month, not days in a week. That was a typo.  I don't know about your other questions though. In the end I want to make a row for each person that counts how many times they were present for consecutive days and how many times they were present for a single day. But first I just want the two lists...
0
Commented:
Try this for a table a(f1, f2) - f-=ID, f2=Day

f1      f2
1      4
1      5
2      2
2      3
4      3
1      7
1      8
1      10

Query1:
``````SELECT x.f1, x.f2
FROM (SELECT a.f1, a.f2,
nz((select c.f2 from a c where c.f1=a.f1 and c.f2=a.f2+1),-99) as flag_1_more,
nz((select c.f2 from a c where c.f1=a.f1 and c.f2=a.f2-1),-99) as flag_1_less
FROM a
ORDER BY a.f1, a.f2) as x
where (x.flag_1_more-x.f2=1) or (x.f2-x.flag_1_less=1)
``````
Result 1:
f1      f2
1      4
1      5
1      7
1      8
2      2
2      3

Query2:
``````SELECT x.f1, x.f2
FROM (SELECT a.f1, a.f2,
nz((select c.f2 from a c where c.f1=a.f1 and c.f2=a.f2+1),-99) as flag_1_more,
nz((select c.f2 from a c where c.f1=a.f1 and c.f2=a.f2-1),-99) as flag_1_less
FROM a
ORDER BY a.f1, a.f2) as x
where (x.flag_1_more=x.flag_1_less)
``````
Result 2:
f1      f2
1      10
4      3

f1      f2
1      10
1      11
2      6
3      4
3      5
3      6
3      23
1      10

Result 1:
f1      f2
1      10
1      11
3      4
3      5
3      6
Result 2:
f1      f2
2      6
3      23
0

Experts Exchange Solution brought to you by