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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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.
 
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.
Click to expand...

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

John Vinson said:
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.
Click to expand...

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]
Click to expand...
 
c. payne wrote:

c. payne said:
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
Click to expand...

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.

--
 
Many Thanks Jamie, sorted!


Jamie Collins said:
c. payne wrote:

c. payne said:
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
Click to expand...

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.
Click to expand...
 
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

c. payne said:
Many Thanks Jamie, sorted!


Jamie Collins said:
c. payne wrote:

c. payne said:
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
Click to expand...

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.
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Select id of smallest record value 4
Access Passing the value to a table 1
way to subquery for next record in query? 1
Ignore records where field is duplicated 4
SQL or maybe a hardware issue for finding lowest Repair order date or mileage 1
If two fields are equal select only the highest value 2
Inserting records while incrementing an ID field. 1
Default value for a new record 3

Back
Top