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?
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?
ASKER
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...
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
PERFECT A+++++++++++++++++++++++++
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?