Time Range

[Brent]

I have a table that contains time ranges and a classification for those
ranges
ex.tblTimeInterval StartTime EndTime IntervalName
6:00 9:00 MORNING
9:00 12:00 MID-MORNING
12:00 15:00 AFTERNOON
15:00 18:00 MID-AFTERNOON
18:00 21:00 EVENING
21:00 0:00 NIGHT


It has to be dynamically set so that a day can be broken up into any range
of times. I have another table called tblDataNoDuplicates. It contains a
field called Time Stamp. I need to know how to have a function look at the
timestamp field and return what IntervalName it fall in. Any ideas would
be appreciated.

 

[Duane Hookom]

Select tblDataNoDuplicates.*, tblTimeInterval.IntervalName
FROM tblDataNoDuplicates, tblTimeInterval
WHERE TimeValue([Time Stamp]) Between StartTime And EndTime;

 

[Tim Ferguson]

You only need one time column:

EndOfSlice DayTime
06:00 Small Hours

09:00 MORNING
12:00 MID-MORNING
15:00 AFTERNOON
18:00 MID-AFTERNOON
21:00 EVENING
24:00 NIGHT


It has to be dynamically set so that a day can be broken up into any
range of times. I have another table called tblDataNoDuplicates. It
contains a field called Time Stamp. I need to know how to have a
function look at the timestamp field and return what IntervalName it
fall in. Any ideas would be appreciated.

SELECT TOP 1 DayTime
FROM DaySlices
WHERE [Input Time Of Day] < EndOfSlice
ORDER BY EndOfSlice ASC;


Hope that helps


Tim F

 

[Brent]

Here is a sample of the data it is returning. I really appreciate the help.

Query1 StationName Item# TimeStamp IntervalName
STATION#3 12345 8:20 MORNING
STATION#1 12345 8:00 NIGHT
STATION#1 12345 8:00 MORNING
STATION#3 12345 8:20 NIGHT
STATION#2 22222 9:11 MID-MORNING
STATION#1 22222 9:02 NIGHT
STATION#2 22222 9:11 NIGHT
STATION#1 22222 9:02 MID-MORNING
STATION#1 33333 9:10 MID-MORNING
STATION#3 33333 9:35 NIGHT
STATION#3 33333 9:35 MID-MORNING
STATION#1 33333 9:10 NIGHT

Spoke too soon. It is returning multiple values for every time. I
think because there is nothing to link tables together.

Brent

Select tblDataNoDuplicates.*, tblTimeInterval.IntervalName
FROM tblDataNoDuplicates, tblTimeInterval
WHERE TimeValue([Time Stamp]) Between StartTime And EndTime;
--
Duane Hookom
MS Access MVP

I have a table that contains time ranges and a classification for those
ranges
ex.tblTimeInterval StartTime EndTime IntervalName
6:00 9:00 MORNING
9:00 12:00 MID-MORNING
12:00 15:00 AFTERNOON
15:00 18:00 MID-AFTERNOON
18:00 21:00 EVENING
21:00 0:00 NIGHT


It has to be dynamically set so that a day can be broken up into any
range of times. I have another table called tblDataNoDuplicates. It
contains a field called Time Stamp. I need to know how to have a
function look at the timestamp field and return what IntervalName it
fall in. Any ideas would be appreciated.

 

[Duane Hookom]

The Between criteria includes values of both ends. If you have an EndTime of
8:00 and a StartTime of 8:00 you will double-up your records. You could use
Select tblDataNoDuplicates.*, tblTimeInterval.IntervalName
FROM tblDataNoDuplicates, tblTimeInterval
WHERE TimeValue([Time Stamp]) >= StartTime And TimeValue([Time Stamp]) <
EndTime;

--
Duane Hookom
MS Access MVP

Here is a sample of the data it is returning. I really appreciate the
help.

Query1 StationName Item# TimeStamp IntervalName
STATION#3 12345 8:20 MORNING
STATION#1 12345 8:00 NIGHT
STATION#1 12345 8:00 MORNING
STATION#3 12345 8:20 NIGHT
STATION#2 22222 9:11 MID-MORNING
STATION#1 22222 9:02 NIGHT
STATION#2 22222 9:11 NIGHT
STATION#1 22222 9:02 MID-MORNING
STATION#1 33333 9:10 MID-MORNING
STATION#3 33333 9:35 NIGHT
STATION#3 33333 9:35 MID-MORNING
STATION#1 33333 9:10 NIGHT

Spoke too soon. It is returning multiple values for every time. I
think because there is nothing to link tables together.

Brent

Select tblDataNoDuplicates.*, tblTimeInterval.IntervalName
FROM tblDataNoDuplicates, tblTimeInterval
WHERE TimeValue([Time Stamp]) Between StartTime And EndTime;
--
Duane Hookom
MS Access MVP


I have a table that contains time ranges and a classification for those
ranges
ex.tblTimeInterval StartTime EndTime IntervalName
6:00 9:00 MORNING
9:00 12:00 MID-MORNING
12:00 15:00 AFTERNOON
15:00 18:00 MID-AFTERNOON
18:00 21:00 EVENING
21:00 0:00 NIGHT


It has to be dynamically set so that a day can be broken up into any
range of times. I have another table called tblDataNoDuplicates. It
contains a field called Time Stamp. I need to know how to have a
function look at the timestamp field and return what IntervalName it
fall in. Any ideas would be appreciated.

 

[Brent]

Works great.

Thanks
Brent

The Between criteria includes values of both ends. If you have an EndTime
of 8:00 and a StartTime of 8:00 you will double-up your records. You could
use
Select tblDataNoDuplicates.*, tblTimeInterval.IntervalName
FROM tblDataNoDuplicates, tblTimeInterval
WHERE TimeValue([Time Stamp]) >= StartTime And TimeValue([Time Stamp]) <
EndTime;

--
Duane Hookom
MS Access MVP

Here is a sample of the data it is returning. I really appreciate the
help.

Query1 StationName Item# TimeStamp IntervalName
STATION#3 12345 8:20 MORNING
STATION#1 12345 8:00 NIGHT
STATION#1 12345 8:00 MORNING
STATION#3 12345 8:20 NIGHT
STATION#2 22222 9:11 MID-MORNING
STATION#1 22222 9:02 NIGHT
STATION#2 22222 9:11 NIGHT
STATION#1 22222 9:02 MID-MORNING
STATION#1 33333 9:10 MID-MORNING
STATION#3 33333 9:35 NIGHT
STATION#3 33333 9:35 MID-MORNING
STATION#1 33333 9:10 NIGHT

Spoke too soon. It is returning multiple values for every time. I
think because there is nothing to link tables together.

Brent
Select tblDataNoDuplicates.*, tblTimeInterval.IntervalName
FROM tblDataNoDuplicates, tblTimeInterval
WHERE TimeValue([Time Stamp]) Between StartTime And EndTime;
--
Duane Hookom
MS Access MVP


I have a table that contains time ranges and a classification for those
ranges
ex.tblTimeInterval StartTime EndTime IntervalName
6:00 9:00 MORNING
9:00 12:00 MID-MORNING
12:00 15:00 AFTERNOON
15:00 18:00 MID-AFTERNOON
18:00 21:00 EVENING
21:00 0:00 NIGHT


It has to be dynamically set so that a day can be broken up into any
range of times. I have another table called tblDataNoDuplicates. It
contains a field called Time Stamp. I need to know how to have a
function look at the timestamp field and return what IntervalName it
fall in. Any ideas would be appreciated.

 

[onedaywhen]

You only need one time column

Yes but it may be unwise to do so. It is convention to have a start
time and an end time for each row for reasons of data integrity. I know
users never do things like this <g> but say one of the rows gets
deleted. With your one column solution you would get erroneous values
rather than missing values.

Jamie.

--