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 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
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.
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.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |