Grouping by time interval

  • Thread starter Thread starter Barry
  • Start date Start date
B

Barry

Hi, there,

I would like to run an query to group the records by time
interval, say 6:01 a.m. to 12:00p.m; 12:01 p.m. to 6:00
p.m.; 6:01p.m. to 00:00 am; 00:01 a.m. to 6:00 a.m., the
interval may change from time to time, so I would to base
on another table to accomplish the job, please give me
some advice. Thank you.

By the way, I am sorry if you found such message before, I
post it yesterday but I search the newsgroup but I cannot
find the original post, therefore, I decide to post it
again.

Barry
 
in a new field header in your query use:

TimeBand: ([yourtime]*24)\4

group by this field
 
Thank you for your information, how about 6:00 - 9:00,
9:00 to 10:00 and 11:00-00:00 and 00:00-6:00, the interval
are not evenly distributed?

Barry
-----Original Message-----
in a new field header in your query use:

TimeBand: ([yourtime]*24)\4

group by this field

Barry said:
Hi, there,

I would like to run an query to group the records by time
interval, say 6:01 a.m. to 12:00p.m; 12:01 p.m. to 6:00
p.m.; 6:01p.m. to 00:00 am; 00:01 a.m. to 6:00 a.m., the
interval may change from time to time, so I would to base
on another table to accomplish the job, please give me
some advice. Thank you.

By the way, I am sorry if you found such message before, I
post it yesterday but I search the newsgroup but I cannot
find the original post, therefore, I decide to post it
again.

Barry
Click to expand...


.
Click to expand...
 
You need another table, caleld tblIntervalLookup with
fields:
IntLabel, text
IntFrom, DateTime
IntTo, DateTime
and it should look something like this:

Intlabel IntFrom IntTo
6.01 to 12.00 6.01 12.00
12.01 to 15.00 12.01 15.00

Let's assume there is a field MyTime in tblYourTable. You
may try a query like this:

SELECT Y.MyTime, I.IntLabel
FROM tblYourTable AS Y, tblIntervalLookup AS I
WHERE tY.MyTime BETWEEN I.IntFrom AND I.IntTo
ORDER BY I.IntFrom

You may want to add a field for sorting intervals and
check what happens when given times are exactly equal
boundary times (6.01, 12.00, 12.01) and adjust the WHERE
condition a bit.

:-)


-----Original Message-----
Thank you for your information, how about 6:00 - 9:00,
9:00 to 10:00 and 11:00-00:00 and 00:00-6:00, the interval
are not evenly distributed?

Barry
-----Original Message-----
in a new field header in your query use:

TimeBand: ([yourtime]*24)\4

group by this field

Barry said:
Hi, there,

I would like to run an query to group the records by time
interval, say 6:01 a.m. to 12:00p.m; 12:01 p.m. to 6:00
p.m.; 6:01p.m. to 00:00 am; 00:01 a.m. to 6:00 a.m., the
interval may change from time to time, so I would to base
on another table to accomplish the job, please give me
some advice. Thank you.

By the way, I am sorry if you found such message before, I
post it yesterday but I search the newsgroup but I cannot
find the original post, therefore, I decide to post it
again.

Barry
Click to expand...


.
Click to expand...
.
Click to expand...
 
Back
Top