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 21st, 2008, 09:23 AM
Friend of Wrox
 
Join Date: Jun 2005
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default Reporting on Time intervals

Hi All
I have data that shows times from 08:00 and everything in between until 20:00 ie 08:01 08:02 and so on. I need to run a report with intervals of five minutes 08:00 to 08:05 10 records and so on

Regards

Brendan Bartley
__________________
Brendan Bartley
 
Old November 21st, 2008, 04:26 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Does the field you are running this from contain Date/Time, or just time?

In either case, you will want a start time, and and end time, and then do an aggregate query using Count(), like:

SELECT [TimeField], Count(PK) As MyTotal
FROM MyTable
WHERE [TimeField] BETWEEN 'Start Time' and 'End Time'

Your start time would want to be 7:59 and your end time 8:06 for the first count, and then increment those by 5 minutes each. You can do that with DateAdd("n", 5, StartTime) and DateAdd("n", 5, EndTime) if they are the proper field types.

Any ideas yet? Did that help?

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 21st, 2008, 04:28 PM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Sorry, I didn't mention the rest about posting these results to a table on a single line, and then doing the next increment in a loop until EndTime = 20:00.

In that case, you can use a counter instead of Count().

Too much code, not enough elegant SQL.

mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 21st, 2008, 05:51 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

I'm going to go out on a limb and actually disagree with MMcDonal.

That is, I *THINK* that all you want is this:

Code:
SELECT DATEADD( 'n', 
                5 * ( DATEDIFF('n',#08:00#,TimeValue(yourTimeField)) \ 5 ),
                #08:00# ) AS FiveMinuteStart,
       SUM(someFieldToBeAdded) AS Total,
       COUNT(*) AS HowMany
FROM yourTable
WHERE TimeValue(yourTimeField) BETWEEN #08:00# AND #19:59:59#
GROUP BY DATEADD( 'n', 
                  5 * ( DATEDIFF('n',#08:00#,TimeValue(yourTimeField)) \ 5 ),
                  #08:00# ) 
ORDER BY 1
By the by, I used TIMEVALUE() there to ensure this worked even if yourTimeField contains both date and time.

Now...

That code assumes that you only want *aggregate* values for each reporting period. That is SUM()s and/or COUNT()s [or other aggregates, such as AVG(), MIN(), MAX()] for data during the period.

If you simply meant that you wanted to break the report at 5 minute intervals, then that's a simpler task. But I still would not perform one query per 5 minute chunk, as MMcDonal suggested.

(And by the by...since this is Access, clearly you would *NOT* use
    WHERE [TimeField] BETWEEN 'Start Time' and 'End Time'
as MMcDonal suggested. You'd need to use
    WHERE [TimeField] BETWEEN #Start Time# and #End Time#
similar to what I showed, above.

**************

But I guess the fundamental question here is: Just WHAT are you reporting?? Do you need just aggregates, as I show? Or do you need to show full data, just with breaks at each 5 minute interval?

FINAL COMMENT: No matter which you need, *IF* your table happens to have *NO DATA* during *ANY* 5 minute period, AND if you want to so indicate in your report (that is, show the time but then no data), THEN indeed you have to resort to MMcDonal's scheme *OR* (much much more efficiently) add a separate table of 5 minute periods to your DB. That's easy to do and will produce much more efficient overall performance.
 
Old November 24th, 2008, 07:39 AM
Friend of Wrox
 
Join Date: Jun 2005
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Guys
Thanks for both your replies. I am having a problem getting the code to work for both of your replies

Brendan Bartley
 
Old November 24th, 2008, 08:29 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I generally need to preserve missing data, so I use the counter instead of the code ("n" in Access, 'n' in SQL Server) but "n" does not always work in Access, for some reason. Also, yes, with the quick responses, I miss code syntax a lot, so ## instead of '' on the data/time fields.

Which method do you need?



mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 24th, 2008, 10:52 PM
Friend of Wrox
 
Join Date: Jun 2008
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

Brendan: It's hard to help you when you don't give us any more specifics than you have. Just for starters, *DO* you need *ONLY* aggregates? Or do you need all record data???
 
Old November 25th, 2008, 06:15 AM
Friend of Wrox
 
Join Date: Jun 2005
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I just need the total amount of calls every five minutes so 08:00 to 08:05 and then every five minutes after

Regards

Brendan Bartley
 
Old November 25th, 2008, 08:32 AM
Friend of Wrox
 
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

That sounds like you want to preserve 0 as a total amount. There are two ways to do this. Assume this data:

8:01 1
8:02 1
8:04 1
8:09 1
8:16 1
8:17 1

In one method, you would get these results:

8:01 - 8:05 3
8:06 - 8:10 1
8:16 - 8:20 2

In the other method, you would get these results:

8:01 - 8:05 3
8:06 - 8:10 1
8:11 - 8:15 0
8:16 - 8:20 2

The issue is, do you need to preserve those time ranges when 0 records were entered, or can you live without them? Old P posted an elegant solution to the latter requirement, and I posted a codey kludge to the former.

Which do you need?


mmcdonal

Look it up at: http://wrox.books24x7.com
 
Old November 25th, 2008, 08:43 AM
Friend of Wrox
 
Join Date: Jun 2005
Posts: 181
Thanks: 0
Thanked 0 Times in 0 Posts
Default

mmcdonal
The second would probably be the best option

Regards

Brendan Bartley





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.