Records In Time Interval

  • Thread starter Thread starter Brent
  • Start date Start date
B

Brent

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
 
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
 
Thanks I greatly appreciate the help


Stephen Haley said:
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

Brent said:
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
Click to expand...
Click to expand...
 
Back
Top