Selecting records based on a difference between records

K

ktm400

Iam trying to filter records so that I only see records that are more than 30
seconds apart. This is from a time value column. Is there a way to do this?
My column looks like this:
11:57:31 pm
11:58:01 pm
11:58:31 pm
12:01:00 pm
12:01:30 pm

I would want to see:
11:58:31 pm
12:01:30 pm

Thanks for any help
 
J

John Vinson

Iam trying to filter records so that I only see records that are more than 30
seconds apart. This is from a time value column. Is there a way to do this?
My column looks like this:
11:57:31 pm
11:58:01 pm
11:58:31 pm
12:01:00 pm
12:01:30 pm

I would want to see:
11:58:31 pm
12:01:30 pm

Thanks for any help
Click to expand...

Are there other fields in this table? Do you have records from
yesterday or last month? If so, does this field incorporate both the
date and time, or just the time? Also, 11:57:31 and 12:01:30 are more
than 30 seconds apart - why wouldn't they qualify?

John W. Vinson[MVP]
 
K

ktm400

I guess a more accurate way to say it would be that I would only want to see
one record of a group that are less than or equal to 30 seconds apart. There
are other fields, 32 in all, one of which is date.
 
J

John Vinson

I guess a more accurate way to say it would be that I would only want to see
one record of a group that are less than or equal to 30 seconds apart. There
are other fields, 32 in all, one of which is date.
Click to expand...

Well, then I'd suggest a Self Join query. Add this table to the query
grid TWICE; join the two instances on the date field (and any other
grouping field if these times refer to more than one entity). Access
will alias the second instance of your table by appending _1 to the
name. Replace "table" with your actual tablename below:

Put a criterion on [Table_1].[timefield] of

=(SELECT Min([timefield]) FROM
AS STable WHERE
Stable.Datefield = Table.Datefield AND Stable.Timefield >
Table.Timefield)

Then add a calculated column to the query:

TimeDiff: DateDiff("s",
.[Timefield], [Table_1].[Timefield])

and put a criterion of > 30 on it.

John W. Vinson[MVP]
 
K

ktm400

John - how do I join the 2 instances on the date field? When I go to
relationships/show table, it only shows one table.....is there a way to join
in the sequel desgn grid?

John Vinson said:
I guess a more accurate way to say it would be that I would only want to see
one record of a group that are less than or equal to 30 seconds apart. There
are other fields, 32 in all, one of which is date.
Click to expand...

Well, then I'd suggest a Self Join query. Add this table to the query
grid TWICE; join the two instances on the date field (and any other
grouping field if these times refer to more than one entity). Access
will alias the second instance of your table by appending _1 to the
name. Replace "table" with your actual tablename below:

Put a criterion on [Table_1].[timefield] of

=(SELECT Min([timefield]) FROM
AS STable WHERE
Stable.Datefield = Table.Datefield AND Stable.Timefield >
Table.Timefield)

Then add a calculated column to the query:

TimeDiff: DateDiff("s",
.[Timefield], [Table_1].[Timefield])

and put a criterion of > 30 on it.

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

John - how do I join the 2 instances on the date field? When I go to
relationships/show table, it only shows one table.....is there a way to join
in the sequel desgn grid?
Click to expand...

Either in the Query Window, or the SQL window. It's not necessary (or
appropriate in this case, it sometimes is) to do the join also in the
Relationships window.

In the Query GDI, add the table using the "Add Tables" icon. Then add
it AGAIN. The same table will show up twice in the window, the second
instance with _1 appended to its name. Drag the date field from one
instance to the other.

In SQL, the JOIN clause would be something like

SELECT [Table1].[Datefield], [Table1].[Timefield],
[Table2].[Timefield], DateDiff("s", [Table1].[Timefield],
[Table2].[Timefield]) AS TimeGap
FROM YourTable AS Table1
INNER JOIN YourTable AS Table2
ON Table1.Datefield = Table2.Datefield
WHERE DateDiff("s", [Table1].[Timefield], [Table2].[Timefield]) > 30
AND Table2.Timefield =
(SELECT Min([Table3].[Timefield] FROM yourtable AS Table3
WHERE Table3.Datefield=Table1.Datefield
AND Table3.Timefield > Table1.Timefield]);


Air code, untested...

John W. Vinson[MVP]
 
K

ktm400

John - thanks for the help.
I have succesfully done all that now, but I get no records when I run the
query. It looks like this:


SELECT Press_2_1.Time, DateDiff("s",Press_2.Time,Press_2_1.Time) AS
TimeDiff, Press_2.Date
FROM Press_2 INNER JOIN Press_2 AS Press_2_1 ON (Press_2.Date =
Press_2_1.Date) AND (Press_2.ProgramNo = Press_2_1.ProgramNo) AND
(Press_2.BlankID = Press_2_1.BlankID) AND (Press_2.ReadingNumber =
Press_2_1.ReadingNumber)
WHERE (((Press_2_1.Time)=(SELECT Min([time]) FROM [Press_2] AS Stable WHERE
Stable.Date = Press_2.Date AND Stable.Time >
Press_2.Time)) AND ((DateDiff("s",[Press_2].[Time],[Press_2_1].[Time]))>30));

There are definately records in the table. The table is a query of a sql
table because the sql table time and date fields are not time date formatted.
I had to do that in another query.....that is why Iam doing a query of a
query.
Gary




John Vinson said:
John - how do I join the 2 instances on the date field? When I go to
relationships/show table, it only shows one table.....is there a way to join
in the sequel desgn grid?
Click to expand...

Either in the Query Window, or the SQL window. It's not necessary (or
appropriate in this case, it sometimes is) to do the join also in the
Relationships window.

In the Query GDI, add the table using the "Add Tables" icon. Then add
it AGAIN. The same table will show up twice in the window, the second
instance with _1 appended to its name. Drag the date field from one
instance to the other.

In SQL, the JOIN clause would be something like

SELECT [Table1].[Datefield], [Table1].[Timefield],
[Table2].[Timefield], DateDiff("s", [Table1].[Timefield],
[Table2].[Timefield]) AS TimeGap
FROM YourTable AS Table1
INNER JOIN YourTable AS Table2
ON Table1.Datefield = Table2.Datefield
WHERE DateDiff("s", [Table1].[Timefield], [Table2].[Timefield]) > 30
AND Table2.Timefield =
(SELECT Min([Table3].[Timefield] FROM yourtable AS Table3
WHERE Table3.Datefield=Table1.Datefield
AND Table3.Timefield > Table1.Timefield]);


Air code, untested...

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

SELECT Press_2_1.Time, DateDiff("s",Press_2.Time,Press_2_1.Time) AS
TimeDiff, Press_2.Date
FROM Press_2 INNER JOIN Press_2 AS Press_2_1 ON (Press_2.Date =
Press_2_1.Date) AND (Press_2.ProgramNo = Press_2_1.ProgramNo) AND
(Press_2.BlankID = Press_2_1.BlankID) AND (Press_2.ReadingNumber =
Press_2_1.ReadingNumber)
WHERE (((Press_2_1.Time)=(SELECT Min([time]) FROM [Press_2] AS Stable WHERE
Stable.Date = Press_2.Date AND Stable.Time >
Press_2.Time)) AND ((DateDiff("s",[Press_2].[Time],[Press_2_1].[Time]))>30));

There are definately records in the table. The table is a query of a sql
table because the sql table time and date fields are not time date formatted.
I had to do that in another query.....that is why Iam doing a query of a
query.
Click to expand...

You'll also need to include the ProgramNo and BlankID and
ReadingNumber fields in the STable subquery. The *format* of the date
and time fields is irrelevant - what's their *datatype*? If they're
Text strings, the time comparison and DateDiff() functions may not
work; you may need to use CDate() to cast them into Date/Time values.

John W. Vinson[MVP]
 
Top