Reports: how do I Group on ranges of hours?

[Janet]

I am trying to group on 6 hourly ranges (see below) and have the group/report
total the number of log-ins during each time range. I have the report
grouping on each client and then counting the number of log-ins during a one
day period (midnight to midnight) for each client by using one day (24 hrs)
per table and one table (with multiple clients on separate pages) tied to the
report at a time. I change the report parameters each day to use the new
table with the new usage.

These are the time framse I need to count the usage for:
00:00-05:59 06:00-09:59 10:00-13:59 14:00-17:59 18:00-21:59 22:00-23:59

I don't mind if I see each log-in row: I just need the total by client per
time group.

Any ideas? Thanks!

 

[Allen Browne]

Create a table with fields like this:
TheHour Number primary key
BracketID Number required.

Enter 24 records, like this:
TheHour BracketID
====== =======
0 1
1 1
2 1
3 1
4 1
5 1
6 2
7 2
...
23 6

You can now create a query that gives you the hour part of the date, by
entering an expression like this into the Field row:
TheHour: DatePart("h",[AppointTime])
Save the query. You can now join this query with the table above, and group
on the BracketID.

 

[Janet]

Hi Allen,

I worked with the info you sent me and that gave me some ideas to try some
other things I had not thought of.

I found a solution that works from my situation. Not ideal, but MUCH better
than what I had.

Using the same table format I have been using for some months now, the raw
data is:
Date
Time
IP (client)
MAC (individual login)

I started playing with Trial reports using the Wizard and then "tweeking"
them. The basic format I had been using was OK, I changed the the Sorting to:

IP
Date
Time
MAC

and created a Header and Footer for:
IP
Time

and had Access create a Format$Time... formula I was then able to use the
following Group Properties for Time:
Group Header Yes
Group Footer Yes
Group On Minute (This and the next two are the keys to the
answer)
Group Interval 60
Keep Together With First Detail

It groups the data by hour and counts the total logins per hour, and also
counts the logins by day per IP.

Thanks for the idea that sparked other ideas - it really helped!

Janet W

Create a table with fields like this:
TheHour Number primary key
BracketID Number required.

Enter 24 records, like this:
TheHour BracketID
====== =======
0 1
1 1
2 1
3 1
4 1
5 1
6 2
7 2
...
23 6

You can now create a query that gives you the hour part of the date, by
entering an expression like this into the Field row:
TheHour: DatePart("h",[AppointTime])
Save the query. You can now join this query with the table above, and group
on the BracketID.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I am trying to group on 6 hourly ranges (see below) and have the
group/report
total the number of log-ins during each time range. I have the report
grouping on each client and then counting the number of log-ins during a
one
day period (midnight to midnight) for each client by using one day (24
hrs)
per table and one table (with multiple clients on separate pages) tied to
the
report at a time. I change the report parameters each day to use the new
table with the new usage.

These are the time framse I need to count the usage for:
00:00-05:59 06:00-09:59 10:00-13:59 14:00-17:59 18:00-21:59 22:00-23:59

I don't mind if I see each log-in row: I just need the total by client per
time group.

Any ideas? Thanks!