Results 1 to 3 of 3
2014-04-23, 01:15 #1
- Join Date
- Apr 2014
- Thanked 0 Times in 0 Posts
Obtaining 3 different time range counts from one Time field.
I have a table in an Access 2007 database that contains a field which records the time someone called our office. (Ex: 5:35). It records times between 4:30 PM and 10:30 PM in one Column called Time.
This information is entered by the office person who received the call. They enter the time via a Form which has several fields that have to be filled in for each call entry.
The Report is opened by entering Start Date and End Date and generates data pulled from a Query that requires fields filled in by the office staff such as: Date, Time, Sex, Location etc...
The Report then shows all the dates there was activity and what activity occurred on each date. Reports are usually printed out on a monthly (March 1 2014 to March 31 2014) basis to show what activity occurred and how often it occurred during an entire month.
What I am trying to accomplish is getting the report to display three different counts on the Report based on the number of calls that were recorded between 4:30 PM to 6:29PM in one box; 6:30PM to 8:29PM in a second box, and 8:30PM to 10:30PM in the third box.
The Report needs to display: Call count of: between 4:30 - 6:29
between 6:30 - 8:29
between 8:30 - 10:30 ** We don't use AM or PM**
I just can't pull it all together to accomplish this feat. I am even willing to send the database via email to anyone who understands how to do this. Thanks - Kimmy
Last edited by Kimmy; 2014-04-25 at 23:08.
2014-04-23, 02:34 #2
- Join Date
- Jan 2001
- Melbourne, Victoria, Australia
- Thanked 263 Times in 241 Posts
Is your database in Excel? Assuming it is, this is how I would do this.
Add a column to your dataset with a formula that calculates the shift number. Eg. If the Time is in cell A2 the formula might be:
=IF(A2<(1110/1440),"Shift 1",IF(A2<(1230/1440),"Shift 2","Shift 3"))
Then use a Pivot table to summarise the entries and return your counts for each shift.Andrew Lockton, Chrysalis Design, Melbourne Australia
2014-04-24, 19:20 #3
- Join Date
- Aug 2010
- Pa, USA
- Thanked 666 Times in 607 Posts
If Andrew's solution does not help you, email me your file and I will gladly help you out.