Time Range

  • Thread starter Thread starter Brent
  • Start date Start date
B

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.
 
Select tblDataNoDuplicates.*, tblTimeInterval.IntervalName
FROM tblDataNoDuplicates, tblTimeInterval
WHERE TimeValue([Time Stamp]) Between StartTime And EndTime;
 
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.
Click to expand...

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


Hope that helps


Tim F
 
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

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

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


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


Brent said:
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

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

Brent
Duane Hookom said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Works great.

Thanks
Brent
Duane Hookom said:
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


Brent said:
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

Brent said:
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.
Click to expand...
Click to expand...
Click to expand...
 
Tim said:
You only need one time column
Click to expand...

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.

--
 
Back
Top