Loop through table

[DonHind]

I have a table with two fields, one is date/time and the other is on/off. Our machine logs a record every minute. The shift is 12 hrs, so the table has 720 records. The purpose is to monitor the machine every minute to know if it is in operation or shut down. I want to loop through the table and every time the on/off field goes to 0, I want to post the time to a new table and also count the number of 0's until the on/off field changes to a 1. Thus I will capture the time the machine shut down and for how many minutes each time. A typical shutdown is for 15 minutes and we might have several a shift. My goal is to populate a form so the machine operator at the end of his shift can explain why each instance of downtime occured.
This is a bit above my level of expertise with Access. If someone could help me in getting the information from the original table and into a summary table, I think I can do the rest. Thanks in avance for any help

 

[SkipVought]

Why not a query?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[JBinQLD]

or something like this mate:


dim db as database
dim rsTracker as recordset
dim rsSummary as recordset
dim intMinuteCounter as integer
dim blOneOutage as boolean

set db=currentdb
set rsTracker as db.openrecordset("tblTracker",dbopendynaset)
set rSummary as db.openrecordset("tblSummary",dbopendynaset)

rsTracker.movefirst
intMinutecunter=0
blOneOutage=false

do until rstracker.eof
if not rsTracker!OnOff 'This is a false, machine down
intMinuteCounter=intminutecounter+1
if blOneOutage then 'we're still tracking last outage
'do nothing
else
'New Outage
rsSummary.AddNew
blOneOutage=true
endif
rSummary!DownTime= rsTracker!DateTimeField
rsummary!OutageLength=intMinuteCounter
else 'Machine is up
if blOneOutage then 'we were tracking as outage which is done now, save the record we crerated
rsSummary.update
blOneOutage=false
end if
endif
rsTracker.MoveNext

loop

Thi couldd be improved with error checking etc, and it's untested but i think it should do what you want...

JB

 

[JBinQLD]

Sorry mate, when we're done recording an outage we need to reset the intMinuteCounter too. After line

blOneOutage=false

add:

intMinuteCounter=0

Ooops!

I also realise that my logic will result in your results table containing the time the machine restarted together with how ong it as down,rather than the time it broke together with how long it was down. I'm sure you can easily fix this yourself if it matters

 

[DonHind]

Thanks, I'll try this out.