Hourly Data Count??

R

Rog

I have a table with thousands of call log records each with a date and time
received. I would to calculate the number of call logs received for each
hourly time period through the day i.e. 8 to 9, 9 to 10 and so on.

I am struggling to get started. Can anyone give me a pointer please?

Regards.
Roger.
 
G

Guest

Roger,

Access stores date values as the decimal portion of a double. From your
question it sounds like you're keeping the date and time in seperate fields.
If that's the case, I would suggest making them one field.

However, if you are using a date/time field to hold the time value, you can
simply use the hour([yourtimefield]) to query the hour (0-23).

Mark
 
M

Marshall Barton

Rog said:
I have a table with thousands of call log records each with a date and time
received. I would to calculate the number of call logs received for each
hourly time period through the day i.e. 8 to 9, 9 to 10 and so on.


Try something like this:

SELECT DateValue(datefield) +
TimeSerial(Hour(datefield),0,0) As Hour,
Count(*) As Calls
FROM thetable
WHERE DateValue(datefield) = [Enter Date]
GROUP BY DateValue(datefield) +
TimeSerial(Hour(datefield),0,0)
 
P

PC Datasheet

Create a query that includes the primary key of your call table in the first
field of the query. Enter the following expression in the second field of
the query:
CallHour:Hour([CallTime]) & " to " & Hour([CallTime]) +1
Click on the Sigma (looks like E) button on the toolbar at the top of the
screen. Under your primary key change Group By to Count.
Run the query.
 
R

Rog

Many thanks, you don't know how complicated I could have made doing that!!
Regards.
Roger.

PC Datasheet said:
Create a query that includes the primary key of your call table in the first
field of the query. Enter the following expression in the second field of
the query:
CallHour:Hour([CallTime]) & " to " & Hour([CallTime]) +1
Click on the Sigma (looks like E) button on the toolbar at the top of the
screen. Under your primary key change Group By to Count.
Run the query.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Rog said:
I have a table with thousands of call log records each with a date and time
received. I would to calculate the number of call logs received for each
hourly time period through the day i.e. 8 to 9, 9 to 10 and so on.

I am struggling to get started. Can anyone give me a pointer please?

Regards.
Roger.
 
P

PC Datasheet

Roger,

If you ever need outside help, please keep me in mind. My fees are very
reasonable.


Rog said:
Many thanks, you don't know how complicated I could have made doing that!!
Regards.
Roger.

PC Datasheet said:
Create a query that includes the primary key of your call table in the first
field of the query. Enter the following expression in the second field of
the query:
CallHour:Hour([CallTime]) & " to " & Hour([CallTime]) +1
Click on the Sigma (looks like E) button on the toolbar at the top of the
screen. Under your primary key change Group By to Count.
Run the query.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Rog said:
I have a table with thousands of call log records each with a date and time
received. I would to calculate the number of call logs received for each
hourly time period through the day i.e. 8 to 9, 9 to 10 and so on.

I am struggling to get started. Can anyone give me a pointer please?

Regards.
Roger.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top