Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
| Search | Today's Posts | Mark Forums Read
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
  #1 (permalink)  
Old October 3rd, 2006, 09:39 AM
Registered User
 
Join Date: Sep 2006
Location: , , .
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default SQL Code for counting operational hours

I have time-stamped data that is recorded while a piece of hardware is in operation (which is intermittent). The data looks something like this:

Time
1/2/06 1:00am
1/2/06 2:00am
1/2/06 2:30am
1/2/06 3:00am
1/4/06 9:00pm
1/4/06 10:00pm

From this data, I want to count the number of hours the unit is in operation to get this:

Hours Time
1 1/2/06 1:00am
2 1/2/06 2:00am
2 1/2/06 2:30am
3 1/2/06 3:00am
4 1/4/06 9:00pm
4 1/4/06 10:00pm

The data will always be in chronological order, but note that there are sometimes multiple entries per hour so I can't use a simple count function and it is not continuous operation so I can't use a time elapse function.

Is there a way to count this using SQL code in Access or do I have to export the data to excel to count?

Thanks.


  #2 (permalink)  
Old October 3rd, 2006, 12:02 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

First thing's first:

Where is the data coming from, and under what parameters? Is the equipment sending stamps when it starts and when it stops? Or intermittently based on what event? Without knowing this, you can't process your data. For example, if it sends a start time, some intermittent times, and an end time, then the equipment ran for 2 hours on 1/2/2006, not 3 hours.

Also, will it always reliably send a start and stop time?

Also, can the equipment ever run for many days at a time continously?

Your best bet is to query for the high and low on each day and then do a DateDiff(), but if it can go for weeks at a time, this will need to be tweaked.

Also, how can you tell which is a start and which is a stop time? Can you have the equipment report differently to a start time and stop time column?

HTH

mmcdonal


Similar Threads
Thread Thread Starter Forum Replies Last Post
Accummulated Man-Hours mateenmohd SQL Server 2000 0 July 15th, 2005 11:31 AM
Check to See if Current Time is between hours mvollmer Classic ASP Professional 5 July 28th, 2004 07:49 PM
9 hours later still trying to do a login script sand133 VB.NET 2002/2003 Basics 5 June 24th, 2004 10:26 AM
code to force xls file to open after several hours helmekki Excel VBA 1 June 16th, 2004 02:42 AM





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