Calculating repeat based on criteria

[Firasath]

Hi,


I've a Calls data and the requirement is to calculate repeated calls based on the below criteria.


Repeat Calls = Same call from same number (Clip) and for Same Product and within last 3 days.

[TABLE="width: 232"]
<tbody>[TR]
[TD]Clip[/TD]
[TD]Calling Date[/TD]
[TD]Product[/TD]
[/TR]
[TR]
[TD="align: right"]3665642[/TD]
[TD="align: right"]9/3/2015 10:49[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]3829930[/TD]
[TD="align: right"]9/27/2015 14:51[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]4870146[/TD]
[TD="align: right"]9/9/2015 12:15[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5025501[/TD]
[TD="align: right"]9/27/2015 16:59[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5025516[/TD]
[TD="align: right"]9/3/2015 19:10[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5025516[/TD]
[TD="align: right"]9/3/2015 20:13[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5025770[/TD]
[TD="align: right"]9/10/2015 9:22[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5025994[/TD]
[TD="align: right"]9/14/2015 13:33[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5035642[/TD]
[TD="align: right"]9/12/2015 9:30[/TD]
[TD]HS[/TD]
[/TR]
[TR]
[TD="align: right"]5035642[/TD]
[TD="align: right"]9/12/2015 9:30[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5035678[/TD]
[TD="align: right"]9/6/2015 11:23[/TD]
[TD]MS[/TD]
[/TR]
[TR]
[TD="align: right"]5035678[/TD]
[TD="align: right"]9/6/2015 12:35[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5035678[/TD]
[TD="align: right"]9/11/2015 18:02[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]5035678[/TD]
[TD="align: right"]9/23/2015 21:21[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]6225816[/TD]
[TD="align: right"]9/26/2015 14:20[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]6358231[/TD]
[TD="align: right"]9/27/2015 22:10[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]6358231[/TD]
[TD="align: right"]9/30/2015 19:48[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]6362841[/TD]
[TD="align: right"]9/9/2015 11:02[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]6384177[/TD]
[TD="align: right"]9/12/2015 14:42[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]6396981[/TD]
[TD="align: right"]9/26/2015 10:06[/TD]
[TD]Others[/TD]
[/TR]
[TR]
[TD="align: right"]6442061[/TD]
[TD="align: right"]9/12/2015 13:59[/TD]
[TD]HS[/TD]
[/TR]
[TR]
[TD="align: right"]6525701[/TD]
[TD="align: right"]9/24/2015 11:17[/TD]
[TD]Others[/TD]
[/TR]
</tbody>[/TABLE]

If the same number (Clip) is appearing in combination of same Product within 3 days period prior to calling Date then it would be considered as Repeat call.
I've tried below code but did not get the result. It is being advised to me by one of my acquaintances who is good in programming. Kindly assist me to correct it or provide alternative solution.

[COLOR=#000000][FONT=Consolas]Option Compare Database[/FONT][/COLOR]

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, sans-serif; white-space: inherit;">[COLOR=#000000]Option Explicit

Private Sub Command0_Click()

Dim db As Database
Dim rs As DAO.Recordset

Dim sql As String

sql = " SELECT tblPrestige.Clip, tblPrestige.Product, tblPrestige.[Cch Date] " _
        & " From tblPrestige " _
        & " GROUP BY tblPrestige.Clip, tblPrestige.Product, tblPrestige.[Cch Date] " _
        & " ORDER BY tblPrestige.Clip, tblPrestige.Product, tblPrestige.[Cch Date] "

Set db = CurrentDb
Set rs = db.OpenRecordset(sql)

Dim pClip As Double
Dim pProd As String
Dim pDate As Date

Do While rs.EOF = False

If rs.Fields("Clip") = pClip And rs.Fields("Product") = pProd And DateDiff("d", rs.Fields("[Cch Date]"), pDate) <= 3 Then

WithinSLA rs.Fields("Clip"), rs.Fields("Product"), rs.Fields("[Cch Date]")

End If

rs.MoveNext

pClip = rs.Fields("Clip")
pProd = rs.Fields("Product")
pDate = rs.Fields("[Cch Date]")

Loop

End Sub

Private Sub WithinSLA(clip As Double, product As String, cchDate As Date)

DoCmd.SetWarnings False

Dim sql As String

sql = " Insert into tblPresExceed (Clip,Product,[Cch Date]) " _
    & " Values (" & clip & ", '" & product & "', #" & cchDate & "#) "
    
'' Execute it
DoCmd.RunSQL sql

DoCmd.SetWarnings True
 [/COLOR]</code>[FONT=Helvetica Neue][COLOR=#000000][FONT=Consolas]End Sub[/FONT][/COLOR][/FONT]

 

[HiTechCoach]

If I were dong this I would use a sub query.

See: Subquery basics