Results 1 to 3 of 3
  1. #1
    New Lounger
    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 22:08.

  2. #2
    Super Moderator
    Join Date
    Jan 2001
    Melbourne, Victoria, Australia
    Thanked 295 Times in 267 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

  3. #3
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Pa, USA
    Thanked 800 Times in 729 Posts

    If Andrew's solution does not help you, email me your file and I will gladly help you out.


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts