Comparing Records

[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.

Any and ALL help truly appreciated!
Dale

 

[Michel Walsh]

Hi,


I don't know what is a "return trip", I assume it is the duration
between TimeReceived and TimeComplete (note that generally, time data also
contain the date itself, that makes computation around midnight a charm,
rather than a nightmare)


SELECT *
FROM myTable
WHERE DateDiff("n", TimeReceived, TimeComplete) <= [within how many minutes]
AND TimeReceived BETWEEN [starting date] AND 1+[ending date
(inclusive)]


DateDiff return the difference in time (in minutes for "n", "m" is for
months), but does not take into account the daylight time saving (use UTC to
avoid that problem, if possible). Still assuming that TimeReceived contains
the date, and the time, the second part of the criteria is almost self
described. Note that today at 6:00:00 is technically greater than today (
at 0:00:00), I add 1 day (24 hour) to get the whole ending date, up to
23:59:59...



Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Hi,


Then you clearly need a second reference to the table.

SELECT a.*, b..*
FROM myTable as a INNER JOIN myTable as b
ON a.CustID = b.CustID
AND a.callDate=b.CallDate
AND a.TimeReceived < b.TimeReceived




The inner join will removes the records appearing just once. The selected
field associated to b are the one occurring after those associated to a.
I assumed there are at most TWO records having the same CustID and callDate.


Hoping it may help,
Vanderghast, Access MVP

A Return Trip is another call to the same location in which the original

problem was not solved. So it would be entered as a new record.

I need to know (in minutes) the difference between the original trip

(record 1) TimeCompleted and the TimeReceived on the 2nd trip (record 2).