Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2003
    Location
    UK
    Posts
    11

    Red face Unanswered: Date and time grouping

    Hi is there anyone who can help with this please.

    I want to run a report from a list dates and times. The date fromat is

    01/01/2007 00:10 :53. The information is entered in to the system via a scanner and is stored in 1 field.

    What I want to report is basically how many scans per hour were carried out. The grouping being done by the hour. Is there a simple way of doing this please?

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    I created a table like this


    ScanTime......................... Item#
    1/25/2007 8:26:04 AM........ 1
    1/25/2007 8:26:12 AM........ 2
    1/25/2007 9:26:18 AM........ 3
    1/25/2007 9:26:20 AM........ 4
    1/25/2007 9:26:21 AM........ 5
    1/25/2007 10:26:22 AM.......6
    1/25/2007 10:26:24 AM.......7
    1/25/2007 10:26:25 AM.......8
    1/25/2007 11:26:26 AM.......9
    1/25/2007 12:26:27 PM.......10

    Then wrote this query:

    SELECT Format([ScanTime],"mm/dd/yy") AS [Date], DatePart("h",[ScanTime]) AS [Hour], Count(Table1.[Item#]) AS [CountOfItem#]
    FROM Table1
    GROUP BY Format([ScanTime],"mm/dd/yy"), DatePart("h",[ScanTime]);

    and got these results:

    Date.......Hour...... CountOfItem#
    01/25/07 8........... 2
    01/25/07 9............3
    01/25/07 10......... 3
    01/25/07 11......... 1
    01/25/07 12......... 1

    Does that give you what you're looking for?

    You can, of course, leave the date field out. I just included that to show how to do it by day, by hour.
    Inspiration Through Fermentation

  3. #3
    Join Date
    Aug 2003
    Location
    UK
    Posts
    11
    Hi Redneck
    This is exactly what I'm after. Many thanks for your help

Posting Permissions

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