Records In Time Interval

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brent - 09 Feb 2005 01:48 GMT

I have a database that tracks items that pass trough certain points. Every
transaction is stored in a single table. I need a query to look at each
record and see if there is another record in that table that occurs within a
designated time frame. ie 20 minutes.

The field names are time and ID#

Any ideas on how to search all records in this database and determine which
ones are matches?

Thanks

Reply to this Message

Stephen Haley - 09 Feb 2005 15:26 GMT

Play with the datediff function
eg for a table named table1 with an Primary field called ID and a date field
called dt

SELECT Table1.id, Table1.dt, Table1Dup.id AS Dupid, Table1Dup.dt,
DateDiff("n",[table1].[dt],[table1dup].[dt]) AS Expr1
FROM Table1, Table1 AS Table1Dup
WHERE (((Table1Dup.id)<>[table1].[id]))
ORDER BY Table1.id, Table1Dup.id;

This will give you a straight query but you may need to go to seconds if you
want omit ones up to 29 secs after.

rgds
Stephen

>I have a database that tracks items that pass trough certain points.
>Every transaction is stored in a single table. I need a query to look at
[quoted text clipped - 7 lines]
>
> Thanks

Reply to this Message

Brent - 09 Feb 2005 15:30 GMT

Thanks I greatly appreciate the help

> Play with the datediff function
> eg for a table named table1 with an Primary field called ID and a date
[quoted text clipped - 23 lines]
>>
>> Thanks

Reply to this Message