Time Range Interval

[Thomas]

Difficult to explain: I am trying to write a query that will display a time
interval rather than an actual time. I have two tables. Table 1 is the raw
data. One of the fields has the actual time an event occurred. I created
Table 2 for interval definitions with 3 fields. Field 1 is the intervalID
which would be 12:00AM, 12:30AM, 1:00AM, etc. (30 minute intervals). Field
2 is the begin qualifier 0:00:00, 0:30:00, 1:00:00, etc. Field 3 is the end
qualifier 0:29:59, 0:59:59, 1:29:59, etc. I created this so that I could
assign an intervalID to each record in table 1. For example, if a record in
Table 1 has a time equal to 16:49:56 (4:49pm +56 seconds), then it should
have intervalID 4:30PM - because it is between the begin qualifier 16:30:00
and the end qualifier 16:59:59 having intervalID 4:30PM.
I am trying to create a query that has these two source tables so that I can
have all records from table 1, and add a field to the end that would indicate
the interval the record took place.

----TABLE 1---- ----TABLE 2----
TIME INTERVAL_ID
FIELD2 BEGIN
FIELD3 END

----QUERY1----
TIME
FIELD2
FIELD3
INTERVAL_ID <-- HERE, I WANT THE 12:00AM, 12:30AM, 1:00AM, ETC. DEPENDING
ON WHERE IT FALLS IN BEGIN AND END TIME QUALIFIERS.

I HOPE THAT'S A CLEAR EXPLANATION?!



THANKS!!!!!!

THOMAS

 

[KARL DEWEY]

SELECT ThomasTable1.Data1, ThomasTable1.Data2, ThomasTable1.EventTime,
ThomasTable2.Begin, ThomasTable2.End, ThomasTable2.INTERVAL_ID
FROM ThomasTable1, ThomasTable2
WHERE (((ThomasTable2.Begin)<=[EventTime]) AND
((ThomasTable2.End)>=[EventTime]));

 

[Marshall Barton]

Difficult to explain: I am trying to write a query that will display a time
interval rather than an actual time. I have two tables. Table 1 is the raw
data. One of the fields has the actual time an event occurred. I created
Table 2 for interval definitions with 3 fields. Field 1 is the intervalID
which would be 12:00AM, 12:30AM, 1:00AM, etc. (30 minute intervals). Field
2 is the begin qualifier 0:00:00, 0:30:00, 1:00:00, etc. Field 3 is the end
qualifier 0:29:59, 0:59:59, 1:29:59, etc. I created this so that I could
assign an intervalID to each record in table 1. For example, if a record in
Table 1 has a time equal to 16:49:56 (4:49pm +56 seconds), then it should
have intervalID 4:30PM - because it is between the begin qualifier 16:30:00
and the end qualifier 16:59:59 having intervalID 4:30PM.
I am trying to create a query that has these two source tables so that I can
have all records from table 1, and add a field to the end that would indicate
the interval the record took place.

----TABLE 1---- ----TABLE 2----
TIME INTERVAL_ID
FIELD2 BEGIN
FIELD3 END

----QUERY1----
TIME
FIELD2
FIELD3
INTERVAL_ID <-- HERE, I WANT THE 12:00AM, 12:30AM, 1:00AM, ETC. DEPENDING
ON WHERE IT FALLS IN BEGIN AND END TIME QUALIFIERS.

Your end field does not pick up times such as 16:59:59.5
This may not bother you much, but it is easy to overcome by
using the next begin time. e.g.
"12:00" 12:00 12:30
This way, it leaves no uncoverd gaps and only requires a
trivial change to the ON expression in this kind of non-equi
Join query:

SELECT Table1.[Time], Table1.FIELD2, Table1.FIELD3,
Table2.INTERVAL_ID
FROM Table1 INNER JOIN Table2
ON Table1.[Time] >= Table2.Begin
And Table1.[Time] < Table2.End

You can not do that in the query design grid so you will
have to work in SQL voew.

Technically, you should not have the END field in Table2
because it it redundantly repeated in the next record. If
you get rid of the End field, you can then use this kind of
query:

SELECT Table1.[Time], Table1.FIELD2, Table1.FIELD3,
(SELECT TOP 1 Table2.INTERVAL_ID
FROM Table2
WHERE Table2.Begin <= Table1.[Time]
ORDER BY Table2.Begin DESC
) As INTERVAL_ID
FROM Table1