How to analyze differences between same field values in multiple records

[Peter Hill]

My database query returns records of specific events sorted by a geographic area and then by date.

Event Date Location
1 9/1/2009 New York
2 10/15/2009 New York
3 2/15/2010 New York
4 3/1/2010 New York
5 8/15/2009 Los Angeles
6 11/12/2009 Los Angeles
7 12/1/2009 Los Angeles
8 12/25/2009 Los Angeles

I am trying to have a query or report return or calculate the following:

Calc 1. The number of days between an event and the preceding event at the same location
Calc 2. Using the data returned in 1., flag occasions when the frequency of events occur within a certain timespan (example below, "IF" CALC1 is <30)

Event Date Location CALC1 CALC2
1 9/1/2009 New York ---
2 10/15/2009 New York 44
3 2/15/2010 New York 123
4 3/1/2010 New York 14 1
5 8/15/2009 Los Angeles ---
6 11/12/2009 Los Angeles 89
7 12/1/2009 Los Angeles 19 1
8 12/25/2009 Los Angeles 24 1

This is easy in a spreadsheet, but I want to have the database do the work

[TheSmileyCoder]

If you make your query so that it sorts by Location and then by date, you can add a calculated field:

NrDays: CalcDate([Location];[Date])

where CalcDate is a VBA function you have to place in a module:

Public Function calcDate(strLoc As String, dtDate As Date) As Integer
    Static strLastLoc As String
    Static dtLastDate As Date
 
    If strLastLoc = strLoc Then
        calcDate = DateDiff("d", dtLastDate, dtDate)
        dtLastDate = dtDate
        Else
        strLastLoc = strLoc
        dtLastDate = dtDate
        calcDate = 0
    End If
End Function

I dont know a smart way of having your second calculated field work within the same query, but you can make a new query based on this query, and then simply have a field there:

toOften:nrDays<30

[Peter Hill]

Thanks for the answer.