Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
| FAQ | Members List | 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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Thread Tools Search this Thread Display Modes
  #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:

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?


Reply With Quote
  #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

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?


Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

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

All times are GMT -4. The time now is 03:23 AM.

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