CODEWITHSUNDEEP
ms-access
rbhalla
rbhalla

Reputation: 1189

How to group records in date ranges based on a certain field

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.

Upvotes: 0

Views: 2045

Answers (1)

PowerUser
PowerUser

Reputation: 11791

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.

Edit

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:

  1. 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

  2. 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 <> rst.Fields("MyPerson") Then EventID = EventID + 1
    LastPerson = rst.Fields("MyPerson")
    rst.Edit
    rst.Fields("EventID") = EventID
    rst.Update
    rst.MoveNext
Wend
docmd.setwarnings true
End Sub

  3. On the On Load event of your form, add in the 2 queries and the sub routine from #1 and #2.

  4. Your final query will be:

    Select MyPerson, EventID, min(MyDate) as StartDate, max(MyDate) as EndDate
from MyTable
group by MyPerson,EventID

Upvotes: 1

Related Questions