Time Range

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Brent - 12 Feb 2005 03:45 GMT

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.

Reply to this Message

Duane Hookom - 12 Feb 2005 04:59 GMT

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

Signature

Duane Hookom
MS Access MVP

>I have a table that contains time ranges and a classification for those
>ranges
[quoted text clipped - 11 lines]
> the timestamp field and return what IntervalName it fall in. Any ideas
> would be appreciated.

Reply to this Message

Brent - 14 Feb 2005 00:35 GMT

Thanks.... Works great!

Brent

> Select tblDataNoDuplicates.*, tblTimeInterval.IntervalName
> FROM tblDataNoDuplicates, tblTimeInterval
[quoted text clipped - 14 lines]
>> function look at the timestamp field and return what IntervalName it fall
>> in. Any ideas would be appreciated.

Reply to this Message

Brent - 14 Feb 2005 01:00 GMT

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

Brent

Reply to this Message

Brent - 14 Feb 2005 02:00 GMT

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.
[quoted text clipped - 18 lines]
>>> function look at the timestamp field and return what IntervalName it
>>> fall in. Any ideas would be appreciated.

Reply to this Message

Duane Hookom - 14 Feb 2005 05:22 GMT

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;

Signature

Duane Hookom
MS Access MVP

> Here is a sample of the data it is returning. I really appreciate the
> help.
[quoted text clipped - 35 lines]
>>>> function look at the timestamp field and return what IntervalName it
>>>> fall in. Any ideas would be appreciated.

Reply to this Message

Brent - 14 Feb 2005 13:56 GMT

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
[quoted text clipped - 43 lines]
>>>>> function look at the timestamp field and return what IntervalName it
>>>>> fall in. Any ideas would be appreciated.

Reply to this Message

Tim Ferguson - 12 Feb 2005 15:46 GMT

You only need one time column:

EndOfSlice DayTime
06:00 Small Hours

> 09:00 MORNING
> 12:00 MID-MORNING
[quoted text clipped - 8 lines]
> 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

Reply to this Message

onedaywhen - 14 Feb 2005 16:35 GMT

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

--

Reply to this Message