Cluster together seperate request events for same client within timespan

[sween1978]

Hi,

Can anyone help with VBA code for the following please. I am wanting to Cluster together separate Request Events that occur for the same Client, within a short
timespan, into one combined Request Event to avoid over-counting Request activity.

As per the example below. What i need to do is have access combine the first two event start dates as they occur within 4 days but only keep the earliest by client id. This would be from a huge list of client id's and dates.

Not even sure if access can do it.

Data looks like this for example

Person id Event Start
1 01/04/2024
1 03/04/2024
1 16/04/2024

 

[Mark_]

Would need a LOT more information on the business application for "Cluster together". Please list the exact business rule(s) you are trying to enforce.

 

[sween1978]

Hi, this is what i am being asked:

Cluster together separate Request Events that occur for the same Client, within a short
timespan, into one combined Request Event to avoid over-counting Request activity.
A new Request Event starting within 4 days of a previous Request Event ending is considered
to be the same Request for purposes of clustering

So request Events are Dates by a specific client id. These are in a table on separate rows.

Below example the first two lines would be classed as a the same request for clustering as its within 4 days. I would need access to keep the first line and remove the 2nd.

 

[Mark_]

So in the form you are entering request events, look to see if there is a previous event (within 4 days) and update that instead? Or are you looking for something different?

The issue with your post is I don't know if you want to combined existing data or future data. For existing data, how do you handle events on 01/01/24, 03/01/24, and 06/01/24. How do they get "clustered"?

Also, do you need to start from today and go in descending order by date OR start with your first entry and work forward in ascending order? Is this for reporting purposes OR are you updating the data?

Depending on what end result you want changes what help can be given.

 

[The_Doc_Man]

This is a "group-by" problem but you have a non-standard grouping that really bollixes up the works. A question you have to ask is this:

Suppose you have a client who calls in on April 3rd, April 7th, and April 10th. Is that two events starting April 3rd (2 calls) and April 10th (1 call)? OR is it two events starting April 3rd (1 call) and April 7th (2 calls)? Because the 3rd and 10th are 7 days apart, but that bugger on the 7th is with 4 days of either of the other two events. Or... because each individual call was within 4 days of its predecessor, is that ONE event with three calls? We need to know the rules here.

In most help desk situations I have seen where call activity is logged, you issue a ticket with an ID number and tell the customer to call back for help but remember the ticket number. Then ALL calls (any date whatsoever) link back to the master ticket ID for the problem, regardless of how many days it took to resolve it.

 

[MajP]

You can do this, But best would be to provide some real data in a table form to do a demo. An excel export with several groups of records.

The solution will need a recordset. If you want to combine data from multiple records and merge records, you would do that if there never should have been multiple entries within the same time period. But if you simply want to cluster information for display then you will likely need a temp table. I think it would be very hard to group "close records" using simply a report or query.
I would think doing this in a temp table would be relatively easy if you can provide some source information.

 

[LarryE]

Suppose you have 5 events occurring on 3 different days as below. Do you want to count 10 events happening on Monday, April 1, 2024 because the next event date is within 4 days of the first one? You said you didn't want to "over count event activity". I am not sure what is being over-counted.

 

[sween1978]

Hi, i have attached an example table. So initially data needs to be sorted by "Person id" and "Event Start Date". I have highlighted cells as they are within 4 days of each other. What i need it to do is remove the latest one and keep the oldest date for the same person id. So remove the line 5 and keep 4. Hope this makes sense. What i am being asked to do is if they are within 4 days of each other than to class the event as 1 for each person. It is a new request starting within 4 days of the previous so it always needs to be matching to the previous. Maybe an If formula between the lines example iif(b5=b4,[d5-d4],"") any that count as 4 or less days can be removed?

 

[Mark_]

Step 1 would be to set up a query on your table. Query would be on Person ID, your primary key, and [Event Start Date].
Step 2 is to call this query with a where clause based on your current record's [Event Start Date] that returns all entries within 4 days of your current [Event Start Date]
Step 3 - IF this query has a record in it, do what you need to do to say "This is matching". I don't know if you need this to stop adding a new record, delete the record you are getting ready to import, or filter it out of a report. I don't know what you need to do with the data, so I can't say the best way to do this.

If you just need to delete, you can include a Dcount() on the query above in a new query to identify records that need to be deleted. Doing this one time you wouldn't care if the query is slow. Also means you would want to run the query without deleting anything just to make sure you have all of the records you really need removed and NOT any that need to stay.

 

[CJ_London]

Your example does not include one with a date of 08/04/2024. If there was, what is to happen with that one? Exclude it because it I is within 4 days of the 5th? Or include because the 5th is excluded?

 

[sween1978]

Your example does not include one with a date of 08/04/2024. If there was, what is to happen with that one? Exclude it because it I is within 4 days of the 5th? Or include because the 5th is excluded?

it would exclude it. if there were several within 4 days of each other for same person id then it would remove all but the initial one if that makes sense

 

[CJ_London]

So how would you do that in excel?

I don’t have access to a computer at the moment but in access I think I would be looking at a left join to itself to return records without a record 4 days previously - something like

Select a.*
From mytable a left join mytable b
On a.person=b.person and b.date >a.date-4 and b.date<a.date
Where b.person is null

Edit- if this is a large table, ensure both person and date are indexed

 

[sween1978]

Hi, only way i can think it might work would be to use an If formula. Custom sort by person id and then event start date and then this formula iif(b5=b4,[d5-d4],"") Any then that show as less than 4 or less can be removed. May its better to take out to excel and then bring back into access .

 

[sween1978]

So how would you do that in excel?

I don’t have access to a computer at the moment but in access I think I would be looking at a left join to itself to return records without a record 4 days previously - something like

Select a.*
From mytable a left join mytable b
On a.person=b.person and b.date >a.date-4 and b.date<a.date
Where b.person is null

Edit- if this is a large table, ensure both person and date are indexed

i think that may have worked. I used this

Select a.*
From mytable a left join mytable b
On a.[person id]=b.[person id] and b.[Event start date] >a.[Event start date]-4 and b.[Event start date]<a.[Event start date]
Where b.[person id] is null

 

[CJ_London]

Just check the 4 days - might need >=4. Also if your date fields included a time element (hidden due to formatting) that will also have an effect on the result

 

[cheekybuddha]

@sween1978 - can you post a db with a table with some representative data to experiment on? Or even a spreadsheet.

This should be doable with a query - either like CJ_London suggests, or using some aggregations.

But it would be handy to have some initial data to work with, even if it's dummy data - I guess just PersonID and the dates are required - then you can verify any solutions give what you expect.

 

[cheekybuddha]

You can try this SQL:

SELECT
  e.[Person id],
  e.[Event Start Date]
FROM YourTable e
INNER JOIN (
  SELECT
    [Person id],
    MIN([Event Start Date]) AS Earliest
  FROM YourTable
  GROUP BY
    [Person id]
) s
        ON e.[Person id] = s.[Person id]
WHERE (DateDiff('d', s.Earliest, e.[Event Start Date]) MOD 4) = 0
GROUP BY
  e.[Person id],
  e.[Event Start Date]
ORDER BY
  e.[Person id],
  e.[Event Start Date]
;

Remeber to change the name of the table to your actual table name.

 

[LarryE]

I used this Original table:

And this Public Function:

Public Function DeleteUnwanted()
On Error GoTo DeleteUnwanted_Error
Dim db As Database
Set db = CurrentDb
Dim PreviousEventDate As Date
Dim PreviousPersonID As Double
Dim TestTable As Recordset
Dim TestFilter As String
TestFilter = "SELECT DISTINCTROW TestTable.TestID, TestTable.PersonID, TestTable.EventStartDate FROM TestTable ORDER BY TestTable.PersonID,  TestTable.EventStartDate;"
Set TestTable = db.OpenRecordset(TestFilter)
With TestTable
    .Edit
    .MoveLast
    .MoveFirst
    'Get the fields of the first record of the TestTable and move to the next record
    PreviousEventDate = TestTable.Fields("EventStartDate")
    PreviousPersonID = TestTable.Fields("PersonID")
    .MoveNext
    Do Until .EOF
        .Edit
        If .Fields("EventStartDate") <= PreviousEventDate + 3 And .Fields("PersonID") = PreviousPersonID Then
            .Edit
            .Delete
            .Requery
            .Edit
        End If
        PreviousEventDate = TestTable.Fields("EventStartDate")
        PreviousPersonID = TestTable.Fields("PersonID")
        .MoveNext
    Loop
.Close
End With
Exit Function

DeleteUnwanted_Error:
DoCmd.CancelEvent
MsgBox Err.Description
Resume Next
'Exit Function
End Function

To get this result in my Test Table:

As you can see, I used a recordset loop to find and delete records that have the same PersonID but where the next EventStartDate is <= the Previous EventStartDate + 3 days.

I have to say I don't know WHY you want to do this, but there it is. I agree with MajP, that using a recordset loop is probably the best and most efficient solution.

Here is the test file also if you want to use it to do testing. Just remember to change your table name, which I do not know.