 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008querying 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  Signatureshirl 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 '==================================================== > 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 [quoted text clipped - 14 lines] > > Shirl 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] [quoted text clipped - 48 lines] > > - Signatureshirl 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 > John > [quoted text clipped - 61 lines] >> >> - John Thanks very much, all sorted now. Shirl John Spencer;2738915 Wrote: > If you just want to show ".00" after the hour then try > [quoted text clipped - 97 lines] > > - Signatureshirl |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.