Repost: Query to records that occured within "X" number of minutes of each other

[Dale]

Access 2000

I am working with a service call database and would like
to create a
query that looks for repeat trips within a user defined
time period in minutes.

The affected fields are CustID, CallDate, TimeComplete,
TimeReceived

The result would show all calls that a return trip was
made within 60 (user entered) minutes within a specified
data range.

For example, a tech may make 4 separate visits to the
same customer within 4 hours (I sure hope not!) because
the problem was not properly resolved on the 1st visit.

Each visit would be a separate record.

So if I say "Show me ALL calls between 5/1/2004 and
5/5/2004 that occured to the same customer (Any Customer)
within 300 minutes of each other.

Keep in mind that the time fields have "11/5/2004 05:30
PM" type formating.

Any and ALL help truly appreciated!
Dale

 

[Ken Snell]

This is actually a fairly complex thing to do. Essentially, you need to
obtain the records for customers who had visits during the time frame
desired, and then you need to "test" each record for a customer against all
other returned records for that same customer to find out when any records
are within the desired minutes of another. This will require a number of
subqueries and temporary tables (most likely) to identify those records.

This is something that would take a number of hours to try to put together a
specific suggestion for you...and unfortunately that goes beyond the time I
personally can devote to your specific question. (This may be why you hadn't
gotten a reply from others previously.)

I can tell you that, in another database that I've written, it took me about
6 hours to design, build, and debug a linked series of queries and a
temporary table (so that I could avoid the use of DLookup and other domain
functions, which are v-e-r-y slow in a query) that were just comparing two,
chronologically sequential records -- in other words, just comparing each
record to the chronologically next record (not to the next 4 or 10 or so
chronologically next records). In that database, I was preparing a report
that showed timeclock data (the elapsed time between chronologically entered
records).

Essentially, you'd need to have a query that obtains all records within your
date range. Likely those records should be written to a temporary table and
that table should have a primary key (autonumber is best) field.

Then you'd need a query (this is the complicated one, and likely it's one
that I would run in code so that I could loop more easily through the
recordset for the comparisons) that compares each record to every other
record (any record whose primary key value is not the same as the current
record's primary key value) and tests the time differential for the
appropriate field (couldn't tell if that should be TimeCompletion or
TimeReceived). If the time differential is within the desired range, both re
cords need to be selected (likely have a boolean true/false field in the
table that can be set by the query -- an update query).

Then you'd need a query that presents the flagged records in the proper
order for a report or form.

Hope that this gets you started in the right direction.