473,325 Members | 2,771 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

Problem grouping records by certain time frame (report)

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
4 2753
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: William Bradley | last post by:
The database I am working on was built by someone else. This database records a production run. When a report is called for this database, the report does not come to the screen but a series of...
1
by: MLH | last post by:
I have a challenge... In a table I call tblStuff4Letters with 3 fields: , and . I have a report called rptOutboundCorrespondence that feeds off this table. Sorting & grouping is turned on in...
7
by: Foxster | last post by:
in access i have two tables (tblplayer, tblpoints) in tblplayer i have field totalpoint (number) in tlbpoints i have field pointgame (number) the two files are related via idplayer now i made a...
3
by: Miguelito Bain | last post by:
hi- i have a simple table. i have 3 fields, id, indate, and outdate. i want to group these fields in my report by month. for example, for february 2004, i want to display all the ids and dates...
4
by: Mal | last post by:
I have an ACC 2000 database that has a strange behaviour I have a small table, with just a few fields... My report has very simple grouping and sorting, no code bar a NODATA event. I have a...
1
by: Megan | last post by:
quick summary: i'm having problems trying to group fields in a report in order to calculate percentages. to calculate percentages, i'm comparing the results from my grouped fields to the totals....
7
by: manning_news | last post by:
I've got a report that's not sorting correctly. I build a SQL statement and assign it to the recordsource in the Open event, sorting the data the way the user chooses. The user can choose up to 3...
4
by: Mal Reeve | last post by:
Hello, I have a report that has only 2 levels of grouping. The detail section is simply 1 large block for a memo field. I am finding that on some occasions the report errors and generates...
8
by: sara | last post by:
I have a report that runs fine with data. If there is no data, I have its NO Data event sending a MsgBox and cancelling the report. Then it seems I still get the 2501 message on the Open Report...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.