Title: How to group records in date ranges based on a certain field
Tags: ms-access
Question: I am currently using a continuous form to a show a set of data that results in the following format (each line being one record):
1/04/13     Person1
31/03/13    Person1
30/03/13    Person1
29/03/13    Person2
28/03/13    Person1
There are 100s of these records and I would like to condense it to the following:
30/03/13 - 1/04/13     Person1
29/03/13               Person2
28/03/13               Person1
The data is from a query, so can be manipulated at the SQL level before it even gets to the form stage. I am also comfortable using macros or VBA to manipulate the form. However, I still haven't been able to find a suitable way of doing this.
I realise that this is typical behaviour for a report, but since I eventually want this to be embedded in another form as a subform, I can't use reports (can't create a subreport in a form unless I am missing something).
Any help or advice is much appreciated.

Here is the accepted answer: Good description of the problem.  Try building your source query like this (assuming the fields are called MyDate and MyPerson and the source table is MyTable):
Select Person, min(MyDate) as StartDate, max(MyDate) as EndDate
from MyTable
group by Person
This gives you 3 fields that you can drop into your form.
Now that we have a more complete picture, the task just got more complicated, but you still have options assuming the 'events' between people don't overlap (if they do, then I can't think of how to create eventIDs afterward).  In the absence of an EventID, you'll have to create one:
First, you need to move your data to a new table so that you can reorder it and add a blank field for the event ID you'll be adding in step 2.
1a. Copy MyTable into a new table. Add an extra field called EventID.
1b. To populate this table, first Delete * from TmpTbl
1c. Then something like:
Insert into TmpTbl.* from MyTable order by MyDate,MyPerson
This code will look through your temp table (TmpTbl as i call it) and add an event id.
Sub AddEventIDs()
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("TmpTbl")
Dim LastPerson As String
Dim EventID As Integer
docmd.setwarnings false
While rst.EOF = False
If LastPerson <&gt; rst.Fields("MyPerson") Then EventID = EventID + 1
LastPerson = rst.Fields("MyPerson")
rst.Edit
rst.Fields("EventID") = EventID
rst.Update
rst.MoveNext
docmd.setwarnings true
End Sub
On the On Load event of your form, add in the 2 queries and the sub routine from #1 and #2.
Your final query will be:
Select MyPerson, EventID, min(MyDate) as StartDate, max(MyDate) as EndDate
from MyTable
group by MyPerson,EventID
Comment for this answer: So person 1 has 2 date ranges you want to display on separate rows. Is there any way to distinguish between these "events"? like, some sort of event ID?
Comment for this answer: That makes sense, there's options here.  But i need to re-ask my question. By 'event id', I mean a way to distinguish each group of 'consecutive days'. In your example, Person A would have 2 unique Event IDs (perhaps 001 and 003) and Person B would only have 1 (perhaps 002). If you don't, and you can't add one to the source table, then the problem just got more complicated. Do you have an event ID like that?
Comment for this answer: Also, can you tell us more about your source table? What other fields are in it, etc.
Comment for this answer: I updated my answer. Basically, you'll have to make your own eventID. If you can logically do that, then it can be grouped. Questions?
Comment for this answer: Thank you for that quick reply, it is extremely close to what I wanted (in fact I immediately changed my form thinking it was exactly what I needed), however, since it groups by person, two different records can show to be covering the same days (when in fact they are not). I have updated my original question to show what I mean since I can't seem to properly show it in this reply. Please let me know if this reply doesn't make much sense.
Comment for this answer: Technically, the number of date ranges can't be determined before hand. With 100s of records, each person could feature multiple times (essentially, I want to be able to group consecutive days in which the same person featured). Also, yes, I do have a unique ID stored for each record
Comment for this answer: Ahh no, I don't have event id's to distinguish grouped ranges. I currently don't have anything sorting these records into groups so no event id's can be generated unfortunately. My table consists of 3 things: a unique id; a date; a person. The person field is linked to another table containing different people records. The fields in that table are arbitrary: unique id; firstname; lastname.
Comment for this answer: Perfect. Well explained and easy to understand what is going on. This is exactly what I was looking for. Sorry it took me a while to reply, silly errors meant I ended up debugging for way too long. Thank you very much, PowerUser!


{'source': 'StackExchangeDataset', 'subset': 'stackoverflow.com', 'metadata': {'AnswerCount': 1, 'CommentCount': 0, 'ContentLicense': 'CC BY-SA 3.0', 'CreationDate': '2013:00:04/08/13', 'FavoriteCount': None, 'PostTypeId': 1, 'Tags': '<ms-access>', 'Title': 'How to group records in date ranges based on a certain field', 'ViewCount': 1873, 'id': 15887569, 'user_AboutMe': '', 'user_AccountId': 1402165.0, 'user_DisplayName': 'rbhalla', 'user_Reputation': 690.0, 'user_Views': 55.0, 'user_id': 1330719.0}}