Summary Report for time fields

  • Thread starter Thread starter Persio Pucci
  • Start date Start date
P

Persio Pucci

Folks,

I am having a problem trying to create a Access 2000 report. Here is the
thing:

I have a table that has phone call records, that is composed of 5 fields,
where one of them is the date and time of the call (dd/mm/yyyy hh:mm:ss) and
another field which is the total call time ([h]:mm:ss).

I would like to create a report with 2 level of groups. First, grouped by
day and then grouped by hour (which takes me to another problem... for
access, 10:29, per example, falls into 10, and 10:31 fall into 11, which
means it's rounding up or down, which is a undesired behavior. I'd like it
to group 10:00 to 10:59 as 10 and 11:00 to 11:59 as 11, and so on).

Here is the major problem. Access won't give me the "summary options" button
after the grouping window, because the fields are not numerical, they are
time fields, and I wanted to see, per example, the total (sum) of call time
per day, as well as the total of phone calls (count) per day, and if I can
go even further, I would like to know what was the hour of most traffic
(like, what hour of the day (10, 11, 12, 13, etc) I had more phone calls
(not necesserily concurrent calls).

Any help will be appreciated, and please excuse me for my poor english! =]

Regards
 
Persio said:
I am having a problem trying to create a Access 2000 report. Here is the
thing:

I have a table that has phone call records, that is composed of 5 fields,
where one of them is the date and time of the call (dd/mm/yyyy hh:mm:ss) and
another field which is the total call time ([h]:mm:ss).

I would like to create a report with 2 level of groups. First, grouped by
day and then grouped by hour (which takes me to another problem... for
access, 10:29, per example, falls into 10, and 10:31 fall into 11, which
means it's rounding up or down, which is a undesired behavior. I'd like it
to group 10:00 to 10:59 as 10 and 11:00 to 11:59 as 11, and so on).

Here is the major problem. Access won't give me the "summary options" button
after the grouping window, because the fields are not numerical, they are
time fields, and I wanted to see, per example, the total (sum) of call time
per day, as well as the total of phone calls (count) per day, and if I can
go even further, I would like to know what was the hour of most traffic
(like, what hour of the day (10, 11, 12, 13, etc) I had more phone calls
(not necesserily concurrent calls).
Click to expand...


After the wizard has taken you as far as it can go, switch
to the report's design view and fix things up yourself.

Take a look in Help for the DatePart function, which can be
used to extract the hour from the date/time of the call.
Then use that in the Sorting and Grouping window (View menu)
=DatePart("h", [calldatefield])
This will take care of grouping on the hour so set the
group's Group On option to Each Value.

You can calculate the summary values in the report footer
section by using text boxes with expressions like:
=Sum([calldatefield]) * 24
to display the total number of hours, and
=Count(*)
to display the number of calls.

Let's wait on the busy hour calculation until after
everthing else is working.
 
After the wizard has taken you as far as it can go, switch
to the report's design view and fix things up yourself.

Take a look in Help for the DatePart function, which can be
used to extract the hour from the date/time of the call.
Then use that in the Sorting and Grouping window (View menu)
=DatePart("h", [calldatefield])
This will take care of grouping on the hour so set the
group's Group On option to Each Value.

You can calculate the summary values in the report footer
section by using text boxes with expressions like:
=Sum([calldatefield]) * 24
to display the total number of hours, and
=Count(*)
to display the number of calls.

Let's wait on the busy hour calculation until after
everthing else is working.
Click to expand...

Marshall,

thank you for you reply! I am doing the report, and it is kind of like the
way I want to... I put in the report, grouped by date, and then, grouped by
hour using the DatePart, I already have the busy calls total time and the
total count.

What is happening now is something weired. It grouped everthing from 00:31
to 01:29 as 01, that ok (I mean, I still want to figure out how to make it
group everythin from 00:00 to 00:59 as 00 and everything from 01:00 to 01:59
as 01, but I can wait on that). However, the next group, which was supposed
to be Hour 02, it shows as Hour 01, but with the Hour 02 results (sum and
count). I can printscreen it and post it somewhere or post the DB file
somewhere you can see... Why is that happening? Any idea how to solve the
hour grouping matter?

Once we are on this matter... I have the call start time (the time and date
used above) and the call duration time. Is there by any chance a way to
figure out what was the maximum of concurrent calls I had on that hour?

Thanks again!

Persio
 
Persio said:
After the wizard has taken you as far as it can go, switch
to the report's design view and fix things up yourself.

Take a look in Help for the DatePart function, which can be
used to extract the hour from the date/time of the call.
Then use that in the Sorting and Grouping window (View menu)
=DatePart("h", [calldatefield])
This will take care of grouping on the hour so set the
group's Group On option to Each Value.

You can calculate the summary values in the report footer
section by using text boxes with expressions like:
=Sum([calldatefield]) * 24
to display the total number of hours, and
=Count(*)
to display the number of calls.

Let's wait on the busy hour calculation until after
everthing else is working.
Click to expand...

Marshall,

thank you for you reply! I am doing the report, and it is kind of like the
way I want to... I put in the report, grouped by date, and then, grouped by
hour using the DatePart, I already have the busy calls total time and the
total count.

What is happening now is something weired. It grouped everthing from 00:31
to 01:29 as 01, that ok (I mean, I still want to figure out how to make it
group everythin from 00:00 to 00:59 as 00 and everything from 01:00 to 01:59
as 01, but I can wait on that). However, the next group, which was supposed
to be Hour 02, it shows as Hour 01, but with the Hour 02 results (sum and
count). I can printscreen it and post it somewhere or post the DB file
somewhere you can see... Why is that happening? Any idea how to solve the
hour grouping matter?

Once we are on this matter... I have the call start time (the time and date
used above) and the call duration time. Is there by any chance a way to
figure out what was the maximum of concurrent calls I had on that hour?
Click to expand...


I don't know how you can be using DatePart and get the hours
grouped by rounded values. Maybe you have something else
going on somewhere? Check to make sure that the groups'
Group On is set to Each Value and Group Interval set to 1.

As for the peak call volume, I'm not much of an SQL expert,
but off the top of my head, it might start with this kind of
query:

SELECT T.calldatefield,
(SELECT Count(*)
FROM table As X
WHERE T.calldatefield Between X.calldatefield
And X.calldatefield + X.duration
) As CallCount
FROM table As T

and a second query to find the maximum

SELECT calldatefield, Max(CallCount) As PeakCalls
FROM firstquery

but there's probably a little more to it than that, so you
may want to post this part of your question to the queries
newsgroup.
 
Back
Top