Calculate number of days between registrations

[John Zelmer]

Simplified, I have the following table:

ID Registration* (primary key)
ID Customer
Registration date
Registration type

so where a customer can have 1-n registrations.

I'm now looking for a way do calculate the average number of days between the consecutive registrations per customer, which of course only can be done if they have more than one registration. For example if customer A registers on 01-01-2015, 09-01-2015 (8 days) and 25-01-2015 (16 days), the average number of days would be 12. I need the actual number of days per comparison as well.

Since I need to compare data from different records in a specific order: Can this somehow be done in one or more queries of should I write code for this?

Thanks in advance for any suggestions!

John

 

[Ranman256]

make a query that sorts the times, the go thru the list and calculate elapsed time.
make a field to store the elapsed time.

Public Sub SetElapseDates()
Dim rst
Dim vDate, vDateOld, vTime

Set rst = CurrentDb.OpenRecordset("qsMyQuery")
With rst
   While Not .EOF
        vDate = .Fields("Registration date").Value & ""
        If vDate <> "" Then
           vTime = DateDiff("d", vDateOld, vDate)

           .Fields("Elapsed").Value = vTime
           .Update
        End If
        
        vDateOld = vDate
       .MoveNext
   Wend
End With
Set rst = Nothing
End Sub

 

[arnelgp]

i tried re-creating your scenario. in the attached db i made to queries, qryRegistration and qryRegistrationFinal. you should look at the two. qryRegistrationFinal is dependent on qryRegistration. i think qryRegistrationFinal is the one you will want to use.

 

[John Zelmer]

Thanks, I wil try and see if I can adjust it so it will work per customer ID.
Regards, John

make a query that sorts the times, the go thru the list and calculate elapsed time.
make a field to store the elapsed time.

Public Sub SetElapseDates()
Dim rst
Dim vDate, vDateOld, vTime

Set rst = CurrentDb.OpenRecordset("qsMyQuery")
With rst
   While Not .EOF
        vDate = .Fields("Registration date").Value & ""
        If vDate <> "" Then
           vTime = DateDiff("d", vDateOld, vDate)

           .Fields("Elapsed").Value = vTime
           .Update
        End If
        
        vDateOld = vDate
       .MoveNext
   Wend
End With
Set rst = Nothing
End Sub

 

[John Zelmer]

Wow, amazing how you did this! I don't understand exactly yet, but I will study on it.
Thank you. John

i tried re-creating your scenario. in the attached db i made to queries, qryRegistration and qryRegistrationFinal. you should look at the two. qryRegistrationFinal is dependent on qryRegistration. i think qryRegistrationFinal is the one you will want to use.