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?
Coaster_brook_troutAsked:
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.

als315Commented:
"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
Coaster_brook_troutAuthor 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
hnasrCommented:
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)

Open in new window

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)

Open in new window

Result 2:
f1      f2
1      10
4      3

Your data:
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

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Coaster_brook_troutAuthor Commented:
PERFECT A++++++++++++++++++++++++++
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.