Is record time field value > n min after the next rec ID

[Guest]

Access 2k database with table with unique IDs and timestamp values for a
single day. Trying to find a way to search records and return the lowest
ID/time where the next record is > 15 minutes after.

 

[John Vinson]

On Tue, 11 Jul 2006 06:55:02 -0700, c. payne <c.

Access 2k database with table with unique IDs and timestamp values for a
single day. Trying to find a way to search records and return the lowest
ID/time where the next record is > 15 minutes after.

There's no such thing as "the next record" in terms of the table. Are
the unique ID's Autonumbers? If so you can't be sure there won't be
gaps!

Try a Subquery with a Subquery:

SELECT <whatever you want to see>
FROM tablename
WHERE ID IN
(SELECT ID FROM tablename AS X
WHERE X.ID =
(SELECT Min(ID] FROM tablename AS Y
WHERE Y.ID > X.ID)
AND DateDiff("n", tablename.[timestamp], X.[timestamp]) > 15)

John W. Vinson[MVP]

 

[Guest]

Thanks for the pointer John I see the reasoning but when I cant seem to get
it to work proprly; it returns no records when it should return ID 105

here are the details:

SELECT tblMain.TIME
FROM tblMain
WHERE (((tblMain.ID) In (SELECT ID FROM tblMain AS X
WHERE X.ID =
(SELECT Min(ID) FROM tblMain AS Y
WHERE Y.ID > X.ID)
AND DateDiff("n", tblMain.TIME, X.TIME) > 15)));

and here is the table contents:
TIME ID
18:25:00 97
18:50:00 98
19:15:00 99
19:40:00 100
20:05:00 101
20:30:00 102
20:55:00 103
21:20:00 104
21:45:00 105
22:10:00 106
22:20:00 107
22:30:00 108
22:40:00 109
22:41:00 110
23:00:00 111
23:10:00 112
23:20:00 113
23:30:00 114
23:40:00 115
23:50:00 116

Many thanks,

C.P.

On Tue, 11 Jul 2006 06:55:02 -0700, c. payne <c.

Access 2k database with table with unique IDs and timestamp values for a
single day. Trying to find a way to search records and return the lowest
ID/time where the next record is > 15 minutes after.

There's no such thing as "the next record" in terms of the table. Are
the unique ID's Autonumbers? If so you can't be sure there won't be
gaps!

Try a Subquery with a Subquery:

SELECT <whatever you want to see>
FROM tablename
WHERE ID IN
(SELECT ID FROM tablename AS X
WHERE X.ID =
(SELECT Min(ID] FROM tablename AS Y
WHERE Y.ID > X.ID)
AND DateDiff("n", tablename.[timestamp], X.[timestamp]) > 15)

John W. Vinson[MVP]

 

[Jamie Collins]

c. payne wrote:

Trying to find a way to search records and return the lowest
ID/time where the next record is > 15 minutes after.

TIME ID
18:25:00 97
18:50:00 98
19:15:00 99
19:40:00 100
20:05:00 101
20:30:00 102
20:55:00 103
21:20:00 104
21:45:00 105
22:10:00 106
22:20:00 107
22:30:00 108
22:40:00 109
22:41:00 110
23:00:00 111
23:10:00 112
23:20:00 113
23:30:00 114
23:40:00 115
23:50:00 116

it should return ID 105

What about ID = 97 (18:25:00)? The next time (18:50:00) is 25 mins
(i.e. > 15 minutes) later and its ID/TIME is lower than 105.

SELECT MIN(DT1.[TIME]) FROM
(
SELECT T1.ID, T1.[TIME], MIN(T2.[TIME])
FROM tblMain AS T1, tblMain AS T2
WHERE T1.[TIME] < T2.[TIME]
GROUP BY T1.ID, T1.[TIME]
HAVING DATEDIFF('n', T1.[TIME], MIN(T2.[TIME])) > 15
) AS DT1

Jamie.

--

 

[Guest]

Many Thanks Jamie, sorted!

c. payne wrote:

Trying to find a way to search records and return the lowest
ID/time where the next record is > 15 minutes after.

TIME ID
18:25:00 97
18:50:00 98
19:15:00 99
19:40:00 100
20:05:00 101
20:30:00 102
20:55:00 103
21:20:00 104
21:45:00 105
22:10:00 106
22:20:00 107
22:30:00 108
22:40:00 109
22:41:00 110
23:00:00 111
23:10:00 112
23:20:00 113
23:30:00 114
23:40:00 115
23:50:00 116

it should return ID 105

What about ID = 97 (18:25:00)? The next time (18:50:00) is 25 mins
(i.e. > 15 minutes) later and its ID/TIME is lower than 105.

SELECT MIN(DT1.[TIME]) FROM
(
SELECT T1.ID, T1.[TIME], MIN(T2.[TIME])
FROM tblMain AS T1, tblMain AS T2
WHERE T1.[TIME] < T2.[TIME]
GROUP BY T1.ID, T1.[TIME]
HAVING DATEDIFF('n', T1.[TIME], MIN(T2.[TIME])) > 15
) AS DT1

Jamie.

 

[Guest]

Now that works, I am trying to use a txtbox value on a form instead of "15"
without much luck. Is "&" concatenation the way to go or is it even possible
with this type of query?

thanks

Many Thanks Jamie, sorted!

c. payne wrote:

Trying to find a way to search records and return the lowest
ID/time where the next record is > 15 minutes after.

TIME ID
18:25:00 97
18:50:00 98
19:15:00 99
19:40:00 100
20:05:00 101
20:30:00 102
20:55:00 103
21:20:00 104
21:45:00 105
22:10:00 106
22:20:00 107
22:30:00 108
22:40:00 109
22:41:00 110
23:00:00 111
23:10:00 112
23:20:00 113
23:30:00 114
23:40:00 115
23:50:00 116

it should return ID 105

What about ID = 97 (18:25:00)? The next time (18:50:00) is 25 mins
(i.e. > 15 minutes) later and its ID/TIME is lower than 105.

SELECT MIN(DT1.[TIME]) FROM
(
SELECT T1.ID, T1.[TIME], MIN(T2.[TIME])
FROM tblMain AS T1, tblMain AS T2
WHERE T1.[TIME] < T2.[TIME]
GROUP BY T1.ID, T1.[TIME]
HAVING DATEDIFF('n', T1.[TIME], MIN(T2.[TIME])) > 15
) AS DT1

Jamie.