By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,745 Members | 1,897 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,745 IT Pros & Developers. It's quick & easy.

Problem grouping records by certain time frame (report)

P: n/a
CH
Hi,
I recently encountered a problem while trying to create a report
grouped by certain time frames. However, Access only allows grouping in
Minutes or Hours. For example, the first time frame is from 5:30am to
6:20am, if I have record 1 with time = 5:30am, record 2 with time =
6:00am and record 3 with time = 6:30am, the report will group record 1,
2 & 3 together under 5:30am (if grouped on Minute) Or group record 1
under 5:30am and record 2 & 3 under 6:00am. The correct output should
group record 1 & 2 under 5:30am and record 3 under 6:30am. Any
suggestion will be welcome. Thank you.

Chris

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
You want to group by each half hour?

In the Sorting'n'Grouping dialog, group by Minutes, and set the interval to
30.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"CH" <cc*****@hotmail.com> wrote in message
news:11**********************@g47g2000cwa.googlegr oups.com...
Hi,
I recently encountered a problem while trying to create a report
grouped by certain time frames. However, Access only allows grouping in
Minutes or Hours. For example, the first time frame is from 5:30am to
6:20am, if I have record 1 with time = 5:30am, record 2 with time =
6:00am and record 3 with time = 6:30am, the report will group record 1,
2 & 3 together under 5:30am (if grouped on Minute) Or group record 1
under 5:30am and record 2 & 3 under 6:00am. The correct output should
group record 1 & 2 under 5:30am and record 3 under 6:30am. Any
suggestion will be welcome. Thank you.

Chris

Nov 13 '05 #2

P: n/a
CH
Hi Allen, thanks for your reply.
I want to group by one and a half hours. Group by Minutes limit the
group interval within hour, which will not be suitable in this case.

Tried using VBA to manually set GroupHeader0.Visible = False to all but
first record (within particular group) if records falls under same
group (Sorting & Grouping Setting: [Time].GroupOn = EachValue,
[Time].GroupInterval = 1), but somehow no headings were shown.

Nov 13 '05 #3

P: n/a
CH wrote:
Hi,
I recently encountered a problem while trying to create a report
grouped by certain time frames. However, Access only allows grouping in
Minutes or Hours. For example, the first time frame is from 5:30am to
6:20am, if I have record 1 with time = 5:30am, record 2 with time =
6:00am and record 3 with time = 6:30am, the report will group record 1,
2 & 3 together under 5:30am (if grouped on Minute) Or group record 1
under 5:30am and record 2 & 3 under 6:00am. The correct output should
group record 1 & 2 under 5:30am and record 3 under 6:30am. Any
suggestion will be welcome. Thank you.

Chris

If we multiply 24*60/90 we get 16 time frames

Now, if we do a
? hour("05:30") + minute("05:30")
the computer echoes back
330
as the number of minutes since midnight

If we divide 330/90, the result is 3.666667. If we do an int on the
result, the number is 3. This is the number I would group on.

You could enter
GroupRank:Int(((Hour([DtTmFld]) * 60) + Minute([DtTmFld]))/90)
with a zero based (0..15) group or add 1 to the Int() value for 1...16.

If you had a table for the 16 time frames, you could link to that to
show the start/end times.

Nov 13 '05 #4

P: n/a
Yes, it should be possible to create a calcuated fields as:
Int(TimeValue([dtTmFld]) * 16)

But I'm not yet convinced that there is a problem with grouping by Minutes,
with an interval of 90.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Salad" <oi*@vinegar.com> wrote in message
news:bs*****************@newsread3.news.pas.earthl ink.net...
CH wrote:
Hi,
I recently encountered a problem while trying to create a report
grouped by certain time frames. However, Access only allows grouping in
Minutes or Hours. For example, the first time frame is from 5:30am to
6:20am, if I have record 1 with time = 5:30am, record 2 with time =
6:00am and record 3 with time = 6:30am, the report will group record 1,
2 & 3 together under 5:30am (if grouped on Minute) Or group record 1
under 5:30am and record 2 & 3 under 6:00am. The correct output should
group record 1 & 2 under 5:30am and record 3 under 6:30am. Any
suggestion will be welcome. Thank you.

Chris

If we multiply 24*60/90 we get 16 time frames

Now, if we do a
? hour("05:30") + minute("05:30")
the computer echoes back
330
as the number of minutes since midnight

If we divide 330/90, the result is 3.666667. If we do an int on the
result, the number is 3. This is the number I would group on.

You could enter
GroupRank:Int(((Hour([DtTmFld]) * 60) + Minute([DtTmFld]))/90)
with a zero based (0..15) group or add 1 to the Int() value for 1...16.

If you had a table for the 16 time frames, you could link to that to show
the start/end times

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.