Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
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 June 17th, 2008, 04:07 AM
Authorized User
Join Date: Jun 2005
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default Help to query for data by 30-mins interval

Hi, I need help to design a query to display the records on a 30-min interval from a table.

The table contains data with different timings (e.g 9:45:42 AM). Is it possible to query and display the count of records on a half-hourly basis, i.e. 7:00 - 7:30AM, 7:31 - 8:30AM, etc?

Thanks in advance.

Old June 19th, 2008, 11:35 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

I am not sure how you would do all of this with a query, but I can help you do it with code if you are interested.

How many half hour itnervals are you interested in reporting on? 48 or 16, or some other number? You could build a local table to hold the results, and then use code to count records from your main table.

Do you need to constrain the data with date ranges as well? Like count all in half hour intervals from today only, or last week, or forever?


Look it up at: http://wrox.books24x7.com
Old June 19th, 2008, 11:52 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

Here is a query solution I came up with.

I have a table with ComputerName, and RunTime for the scan, which is in hours:mins:secs.

I queried it like this (RunMinuteBA):

SELECT dbo_tblComputer.ComputerName, DatePart("h",[RunTime]) AS RunHour, DatePart("n",[RunTime]) AS RunMinute, IIf([RunMinute]<31,"B","A") AS BA
FROM dbo_tblComputer;

This gave me data that looks like this:

ComputerName RunHour RunMinute BA
W1335524 7 40 A
W1335570 5 12 B
W76HQRD1 7 30 B
W5YQHD61 7 32 A
W1372756 7 33 A
W7BBVRD1 7 34 A
W1247789 10 55 A
W51470D1 10 40 A

Then I queried this data like this:

SELECT RunMinuteBA.RunHour, RunMinuteBA.BA, Count(RunMinuteBA.ComputerName) AS CountOfComputerName
FROM RunMinuteBA
GROUP BY RunMinuteBA.RunHour, RunMinuteBA.BA
ORDER BY RunMinuteBA.RunHour, RunMinuteBA.BA DESC;

This gave me this data:

RunHour BA CountOfComputerName
0 B 25
0 A 15
1 B 6
1 A 11
2 B 8
2 A 6
3 B 11
3 A 18

Now if I were to put this on a report, then I would use the Detail section's On Format event to add this to a label:

Me.BA.Visible = False

If Me.BA = B Then
   Me.BALabel = ":00 - :30"
   Me.BALabel = ":31 - :59"
End If

Does that work for you?

Two queries instead of one, but it works.


Look it up at: http://wrox.books24x7.com

Similar Threads
Thread Thread Starter Forum Replies Last Post
tooltip interval??? nerssi HTML Code Clinic 5 August 4th, 2006 12:05 AM
Help for Last 30 Business Days Query srinath2003 SQL Server 2000 3 March 21st, 2005 02:37 PM
interval timed calls to asp routines NickOg Javascript How-To 6 November 5th, 2004 03:55 AM
How to select a time that is within a interval? kuzze MySQL 2 August 23rd, 2004 06:33 AM
A date interval with a date interval query spinto SQL Server 2000 7 May 15th, 2004 04:26 AM

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