Hourly Data Count??

  • Thread starter Thread starter Rog
  • Start date Start date
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.
 
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
 
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.
Click to expand...


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)
 
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.
 
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.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top