records entered within a timeframe?

  • Thread starter Thread starter Rich
  • Start date Start date
R

Rich

how do i create a query to find records entered within a
certain time apart?

example: 1 of the fields in the datebase captures the
date/time a record was entered. i need to identify any
records that were entered into the database under 20
seconds apart?
 
Try a criterion expression similar to this (uses a parameter that asks the
user for the date/time):

Between [Enter the date and time:] And DateAdd("s", 20, [Enter the date and
time:])
 
Hi,
This is the only way I could figure out how to do this.
The statement is rather unwieldy looking but it works on my
limited set of data. I tested it out on a table with only a date/time field and it returns
only those records that are within +- 20 seconds of each other.
My table name is tblSecondsApart and my date/time field is myTime.

You will have to go through the statement and replace these with your names.
My query naturally only returns the date/time field. You can add any other fields
you wish to see at the very beginning of the statement. THis will probably be slow
on a large data set.
Okay, here it is:

SELECT tblSecondsApart.myTime
FROM tblSecondsApart
WHERE ((Not (IIf(DCount("[myTime]","tblSecondsApart","myTime <> #" & [myTime] & "#
AND myTime Between #" & DateAdd("s",-20,[myTime]) & "# And #" & DateAdd("s",20,[myTime]) & "#")>0,[myTime],Null)) Is Null))
ORDER BY IIf(DCount("[myTime]","tblSecondsApart","myTime <> #" & [myTime] & "#
AND myTime Between #" & DateAdd("s",-20,[myTime]) & "# And
#" & DateAdd("s",20,[myTime]) & "#")>0,[myTime],Null);
 
Slick!

--
Ken Snell
<MS ACCESS MVP>

Dan Artuso said:
Hi,
This is the only way I could figure out how to do this.
The statement is rather unwieldy looking but it works on my
limited set of data. I tested it out on a table with only a date/time field and it returns
only those records that are within +- 20 seconds of each other.
My table name is tblSecondsApart and my date/time field is myTime.

You will have to go through the statement and replace these with your names.
My query naturally only returns the date/time field. You can add any other fields
you wish to see at the very beginning of the statement. THis will probably be slow
on a large data set.
Okay, here it is:

SELECT tblSecondsApart.myTime
FROM tblSecondsApart
WHERE ((Not (IIf(DCount("[myTime]","tblSecondsApart","myTime <> #" & [myTime] & "#
AND myTime Between #" & DateAdd("s",-20,[myTime]) & "# And #" &
Click to expand...
DateAdd("s",20,[myTime]) & "#")>0,[myTime],Null)) Is Null))
ORDER BY IIf(DCount("[myTime]","tblSecondsApart","myTime <> #" & [myTime] & "#
AND myTime Between #" & DateAdd("s",-20,[myTime]) & "# And
#" & DateAdd("s",20,[myTime]) & "#")>0,[myTime],Null);

--
HTH
Dan Artuso, Access MVP


how do i create a query to find records entered within a
certain time apart?

example: 1 of the fields in the datebase captures the
date/time a record was entered. i need to identify any
records that were entered into the database under 20
seconds apart?
Click to expand...
Click to expand...
 
Thanks Ken.
I actually spent quite a bit of time building that ugly looking thing!
I started off by trying to do it with some sort of sub-query but had no luck
with that.
God knows how long it will take to run on a table with a substantial amount
of data though.


Dan


Ken Snell said:
Slick!

--
Ken Snell
<MS ACCESS MVP>

Dan Artuso said:
Hi,
This is the only way I could figure out how to do this.
The statement is rather unwieldy looking but it works on my
limited set of data. I tested it out on a table with only a date/time field and it returns
only those records that are within +- 20 seconds of each other.
My table name is tblSecondsApart and my date/time field is myTime.

You will have to go through the statement and replace these with your names.
My query naturally only returns the date/time field. You can add any other fields
you wish to see at the very beginning of the statement. THis will probably be slow
on a large data set.
Okay, here it is:

SELECT tblSecondsApart.myTime
FROM tblSecondsApart
WHERE ((Not (IIf(DCount("[myTime]","tblSecondsApart","myTime <> #" & [myTime] & "#
AND myTime Between #" & DateAdd("s",-20,[myTime]) & "# And #" &
Click to expand...
DateAdd("s",20,[myTime]) & "#")>0,[myTime],Null)) Is Null))
ORDER BY IIf(DCount("[myTime]","tblSecondsApart","myTime <> #" & [myTime] & "#
AND myTime Between #" & DateAdd("s",-20,[myTime]) & "# And
#" & DateAdd("s",20,[myTime]) & "#")>0,[myTime],Null);

--
HTH
Dan Artuso, Access MVP


how do i create a query to find records entered within a
certain time apart?

example: 1 of the fields in the datebase captures the
date/time a record was entered. i need to identify any
records that were entered into the database under 20
seconds apart?
Click to expand...
Click to expand...
Click to expand...
 
Hi Rich,

This works (tblT is the table, T is the date/time field, and PK is a
field that identifies the record):

SELECT A.PK, A.T, B.PK, B.T
FROM tblT AS A INNER JOIN tblT AS B
ON (DateDiff("s", A.T, B.T) >0)
AND (DateDiff("s", A.T, B.T) <=20);

but the need to compare every record with every other mean multitudinous
calls to DateDiff() which make it excruciatingly slow except with a very
small data set. Cutting out DateDiff() makes it faster though more
opaque:

SELECT A.PK, A.T, B.PK, B.T
FROM tblT AS A INNER JOIN tblT AS B
ON (A.T < B.T)
AND (B.T - A.T < 2.31481481481481E-04);

The constant in the last line is the decimal equivalent of 20 seconds in
an Access date/time field. This syntax also works:

SELECT A.PK, A.T, B.PK, B.T
FROM tblT AS A, tblT AS B
WHERE (A.T < B.T)
AND (B.T - A.T < 2.31481481481481E-04);

I don't know which is faster.


how do i create a query to find records entered within a
certain time apart?

example: 1 of the fields in the datebase captures the
date/time a record was entered. i need to identify any
records that were entered into the database under 20
seconds apart?
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
That's okay Ken. I knew there had to be a way to do it.
In fact you got me to try again and it became obvious how do
adapt my logic:

SELECT t1.myTime
FROM tblSecondsApart AS t1
WHERE (
Select Count(t2.myTime) From tblSecondsApart As t2
Where t2.myTime <> t1.myTime And (t2.myTime Between
DateAdd("s",-20,t1.myTime) And DateAdd("s",20,t1.myTime)))> 0
Order By t1.myTime;

although John's solutions are neater.


Dan


Ken Snell said:
Also slick!

(didn't want to appear to be playing favorites between you and Dan!
)


--
Ken Snell
<MS ACCESS MVP>

John Nurick said:
Hi Rich,

This works (tblT is the table, T is the date/time field, and PK is a
field that identifies the record):

SELECT A.PK, A.T, B.PK, B.T
FROM tblT AS A INNER JOIN tblT AS B
ON (DateDiff("s", A.T, B.T) >0)
AND (DateDiff("s", A.T, B.T) <=20);

but the need to compare every record with every other mean multitudinous
calls to DateDiff() which make it excruciatingly slow except with a very
small data set. Cutting out DateDiff() makes it faster though more
opaque:

SELECT A.PK, A.T, B.PK, B.T
FROM tblT AS A INNER JOIN tblT AS B
ON (A.T < B.T)
AND (B.T - A.T < 2.31481481481481E-04);

The constant in the last line is the decimal equivalent of 20 seconds in
an Access date/time field. This syntax also works:

SELECT A.PK, A.T, B.PK, B.T
FROM tblT AS A, tblT AS B
WHERE (A.T < B.T)
AND (B.T - A.T < 2.31481481481481E-04);

I don't know which is faster.


how do i create a query to find records entered within a
certain time apart?

example: 1 of the fields in the datebase captures the
date/time a record was entered. i need to identify any
records that were entered into the database under 20
seconds apart?
Click to expand...

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
Click to expand...
Click to expand...
 
although John's solutions are neater.
Click to expand...

More through ignorance than anything else<g>. It would be interesting to
know which is faster - and more stable: with 4500 records in the dataset
my DateDiff() solution sent Access 2002 into extended bouts of
high-intensity navel gazing (appropriating at least 99% of processor
cycles). The non-DateDiff version was much faster and better behaved.

John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
 
Back
Top