A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

querying time



 
 
Thread Tools Display Modes
  #1  
Old April 20th, 2008, 08:50 PM
shirl shirl is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2005
Posts: 39
Default querying time

I have been given a database, which has been used to record incoming calls, the user enters the time someone phones in, the department name and person they were put through to.

They now want to be able to record the number of calls to departments on an hourly basis and display this in a report, so that they can see which departments get the most calls.

The report is to display the list of calls by date, department and a total number of calls to a department between say 09.00 and 10.00, 10.00 and 11.00 and so on.

Fields in the database are, Caller, date called, time called, contact, department. Time called is short time format.

I'm not sure of the best way to do this, so would welcome suggestions.

Shirl
  #2  
Old April 20th, 2008, 11:09 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 2,364
Default querying time

SELECT Department
, [Date called]
, Hour([Time Called]) as TheHour
, Count([Time Called]) as CallCount
FROM [Your Table]
GROUP BY Department
, [Date called]
, Hour([Time Called])

In the query design grid,
-- Add your table
-- add Department, Date Called, and Time Called (twice)
-- Change the first time called to
Field: TheHour: Hour([Time Called])
-- Select View: Totals from the menu
-- Change GROUP BY to COUNT under the second instance of [Time Called]
Save the query

If you want you can use this query to build a cross tab query to show
the data.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


shirl wrote:
I have been given a database, which has been used to record incoming
calls, the user enters the time someone phones in, the department name
and person they were put through to.

They now want to be able to record the number of calls to departments
on an hourly basis and display this in a report, so that they can see
which departments get the most calls.

The report is to display the list of calls by date, department and a
total number of calls to a department between say 09.00 and 10.00,
10.00 and 11.00 and so on.

Fields in the database are, Caller, date called, time called, contact,
department. Time called is short time format.

I'm not sure of the best way to do this, so would welcome suggestions.

Shirl




  #3  
Old April 23rd, 2008, 11:36 PM
shirl shirl is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2005
Posts: 39
Default

John

Thanks for the reply, I have done what you said and it worked, the only problem I now have is that I want to show the hour in the 24 hour clock, ie 1pm in the afternoon should display as 13.00 currently it is showing as 13

I have looked at the properties in the query and report and there is no option for date/time only numeric, any suggestions?

Shirley

Quote:
Originally Posted by John Spencer View Post
SELECT Department
, [Date called]
, Hour([Time Called]) as TheHour
, Count([Time Called]) as CallCount
FROM [Your Table]
GROUP BY Department
, [Date called]
, Hour([Time Called])

In the query design grid,
-- Add your table
-- add Department, Date Called, and Time Called (twice)
-- Change the first time called to
Field: TheHour: Hour([Time Called])
-- Select View: Totals from the menu
-- Change GROUP BY to COUNT under the second instance of [Time Called]
Save the query

If you want you can use this query to build a cross tab query to show
the data.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


shirl wrote:
I have been given a database, which has been used to record incoming
calls, the user enters the time someone phones in, the department name
and person they were put through to.

They now want to be able to record the number of calls to departments
on an hourly basis and display this in a report, so that they can see
which departments get the most calls.

The report is to display the list of calls by date, department and a
total number of calls to a department between say 09.00 and 10.00,
10.00 and 11.00 and so on.

Fields in the database are, Caller, date called, time called, contact,
department. Time called is short time format.

I'm not sure of the best way to do this, so would welcome suggestions.

Shirl



  #4  
Old April 24th, 2008, 12:23 PM posted to microsoft.public.access.queries
John Spencer
external usenet poster
 
Posts: 7,815
Default querying time

If you just want to show ".00" after the hour then try

SELECT ...
Hour([Time Called]) & ".00" as TheHour
FROM ...
GROUP BY ...
, Hour([Time Called])
, Hour([Time Called]) & ".00"

ORDER BY [Date Called], [Hour Called]

Not that I have included Hour([Time Called]) along with Hour([Time Called]) &
".00". The latter expression will return a string and will sort
alphabetically instead of numerically.

An alternative that I have not tested fully would be to use
Format(Hour([Time Called]),"00.00") as TheHour
If that works you would not need to include Hour([Time Called]) since that
string will sort in the order you want.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

shirl wrote:
John

Thanks for the reply, I have done what you said and it worked, the only
problem I now have is that I want to show the hour in the 24 hour clock,
ie 1pm in the afternoon should display as 13.00 currently it is showing
as 13

I have looked at the properties in the query and report and there is no
option for date/time only numeric, any suggestions?

Shirley

John Spencer;2732676 Wrote:
SELECT Department
, [Date called]
, Hour([Time Called]) as TheHour
, Count([Time Called]) as CallCount
FROM [Your Table]
GROUP BY Department
, [Date called]
, Hour([Time Called])

In the query design grid,
-- Add your table
-- add Department, Date Called, and Time Called (twice)
-- Change the first time called to
Field: TheHour: Hour([Time Called])
-- Select View: Totals from the menu
-- Change GROUP BY to COUNT under the second instance of [Time Called]
Save the query

If you want you can use this query to build a cross tab query to show
the data.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


shirl wrote:-
I have been given a database, which has been used to record incoming
calls, the user enters the time someone phones in, the department
name
and person they were put through to.

They now want to be able to record the number of calls to departments
on an hourly basis and display this in a report, so that they can see
which departments get the most calls.

The report is to display the list of calls by date, department and a
total number of calls to a department between say 09.00 and 10.00,
10.00 and 11.00 and so on.

Fields in the database are, Caller, date called, time called,
contact,
department. Time called is short time format.

I'm not sure of the best way to do this, so would welcome
suggestions.

Shirl



-





  #5  
Old April 25th, 2008, 12:08 AM
shirl shirl is offline
Experienced Member
 
First recorded activity by OfficeFrustration: Mar 2005
Posts: 39
Default

John

Thanks very much, all sorted now.

Shirl

Quote:
Originally Posted by John Spencer View Post
If you just want to show ".00" after the hour then try

SELECT ...
Hour([Time Called]) & ".00" as TheHour
FROM ...
GROUP BY ...
, Hour([Time Called])
, Hour([Time Called]) & ".00"

ORDER BY [Date Called], [Hour Called]

Not that I have included Hour([Time Called]) along with Hour([Time Called]) &
".00". The latter expression will return a string and will sort
alphabetically instead of numerically.

An alternative that I have not tested fully would be to use
Format(Hour([Time Called]),"00.00") as TheHour
If that works you would not need to include Hour([Time Called]) since that
string will sort in the order you want.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County

shirl wrote:
John

Thanks for the reply, I have done what you said and it worked, the only
problem I now have is that I want to show the hour in the 24 hour clock,
ie 1pm in the afternoon should display as 13.00 currently it is showing
as 13

I have looked at the properties in the query and report and there is no
option for date/time only numeric, any suggestions?

Shirley

John Spencer;2732676 Wrote:
SELECT Department
, [Date called]
, Hour([Time Called]) as TheHour
, Count([Time Called]) as CallCount
FROM [Your Table]
GROUP BY Department
, [Date called]
, Hour([Time Called])

In the query design grid,
-- Add your table
-- add Department, Date Called, and Time Called (twice)
-- Change the first time called to
Field: TheHour: Hour([Time Called])
-- Select View: Totals from the menu
-- Change GROUP BY to COUNT under the second instance of [Time Called]
Save the query

If you want you can use this query to build a cross tab query to show
the data.

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


shirl wrote:-
I have been given a database, which has been used to record incoming
calls, the user enters the time someone phones in, the department
name
and person they were put through to.

They now want to be able to record the number of calls to departments
on an hourly basis and display this in a report, so that they can see
which departments get the most calls.

The report is to display the list of calls by date, department and a
total number of calls to a department between say 09.00 and 10.00,
10.00 and 11.00 and so on.

Fields in the database are, Caller, date called, time called,
contact,
department. Time called is short time format.

I'm not sure of the best way to do this, so would welcome
suggestions.

Shirl



-




 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 12:58 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.