Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old November 25th, 2008, 09:55 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

So can you post the starting table and field names, with data types, and how you would like them displayed when the results are returned?
Also table location (local, SQL Server, oter?)

For Ex:

MyTransID - autonumber
TransType - FK
TransOther - Text
TransDate - Date (or text or number?)
TransTime - Time (or text or number?)


Date Time TransCount


Date Time TransType TransCount



Look it up at: http://wrox.books24x7.com
Old November 26th, 2008, 01:14 AM
Friend of Wrox
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

And although it's a tiny bit of a pain, it's well worth creating an 5-minute interval table, as I suggested.

Say you create a table named FiveMinuteIntervals and it has a *single* field named IntervalStart that is DATETIME datatype.

If you just enter the first hour of 5 minute intervals into the table (that is 8:00AM, 8:05AM, ..., 8:55AM ... OR if you want the intervals to start on the 1 minute mark then 8:01AM, 8:06AM, ..., 8:56AM) then you can use a simple query to create the other hours.

Something like this:

INSERT INTO FiveMinuteIntervals
SELECT TOP 12 DATEADD('h',1,IntervalStart)
FROM FiveMinuteIntervals
ORDER BY IntervalStart DESC

And then you just execute that stored query 11 times and you should have 5 minute intervals from 8 am to 8 pm.

*NOW* you can just do
SELECT FMI.IntervalStart, COUNT(T.TransactionTime) AS numberOfCalls
FROM FiveMinuteIntervals AS FMI LEFT JOIN yourMainTable AS T
ON (   DATEDIFF( 'n', #8:00:00 AM#, FMI.IntervalStart ) 
     = 5 * ( DATEDIFF( 'n', #8:00:00 AM#, T.TransactionTime ) \ 5 )
GROUP BY FMI.IntervalStart
ORDER BY FMI.IntervalStart
If your intervals start at 8:01, 8:06, etc. (instead of 8:00, 8:05, as I have assumed) then you would use
    #8:01:00 AM#
in both places there.

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automatically inserting rows at varied intervals Peskarik Excel VBA 0 August 14th, 2008 02:24 AM
Time Shift time in minus time out lechalas Beginning VB 6 1 August 11th, 2008 01:56 PM
time zone & day light time rajn ASP.NET 1.0 and 1.1 Professional 0 August 7th, 2007 05:02 PM
selecting data in datetime intervals Phathu SQL Server 2005 1 August 29th, 2006 03:53 PM
Using xs:time to generate time in desired format krayan001 XSLT 0 June 27th, 2005 04:28 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.