trying to count bats: log transit times and direction

[the_net_2.0]

yes... could you give me a clue as to how it should be done?
p.s. and thanks for all the help so far.

yeah no problem. You know I don't really know, and i don't really have the time right now to think about it. But I really don't understand why the code I posted for you does not fit the job. What's wrong with it? the example to complex to understand?

how many recs are there in the table? you said 30 distinct dates. I understand that. but how many total recs? my guess is, with the speed that vba can execute code, if you had 100,000 records total and 30 loops to perform it would take about 1-3 min. I think that's overstating, but I really have no idea, as I'm not an expert with code execution.

the other thing about extremely long execution times is that vba can seriously corrupt data output if the code runs long enough. that's my experience anyway. what I've done in the past, if I am looping a lot and a loops have to run a long time, is put a break point at the end of every loop to let vba catch up, gather thoughts, restructure memory, etc... whatever you want to call it.

everytime I have done that, the long executions have come out fine. It's kind of a pain to sit there and press the OK button in a msgbox after every loop finishes, but it's better than getting output that's completely incorrect.

 

[gemma-the-husky]

Hi Gemma, the database I inherited works as far as collecting raw data -it is generic for hardware:http://www.ipcas.com/products/serial-ethernet.html
the problem I have is converting the data which is collected into something that is useful.
Thenet2 understands the issue and I am trying to explain it to you: REPLIES IN CAPS below-no offence, Im not shouting!

"not really what if two bats fly in, and none out. what happens to the pins then?
IT IS IRRELEVANT, THE PINS ARE A RECORD OF A TRIGGER OR NOT
presumably at the end of the flying period, they all fly in, and none fly out
THEY FLY IN AND OUT AT DIFFERENT TIMES BUT MOST WILL HAVE RETURNED BY DAWN -NEVER EXACTLy MaTCHING THOUGH
what i mean is it ought to be possible for you by inspecting the data, is to identify what each line means
IT IS NOT POSSIBLE FROM A SINGLE LINE (RECORD) TO DETERMINE ANYTHING BECAUSE YOU NEED FOUR SEQUENTIAL RECORDS TO DETERMINE A SINGLE FLIGHT

Does this help explain better?

not really, i just cant follow what is happening.

do you have 2 beams, that are recording "clicks" when the beams are broken (either by a bat going in or out)?

or are you just sampling the data at very short intervals?

 

[cmp20]

Hi, heres your code (slightly changed -it didnt like DISTINCT etc.)
one problem is I havent worked with access in a few years and im very rusty -and I dont really understand how to work with recordsets anyway -I know sad. so yes, the example is too complex although Im trying to muddle through it..

As regards numbers of rcords: there are approx 600 bats, using old logger data: if each bat transits goes in/out 3x per night = 6 passes, right? 6 passes x 4 records per pass = 24 records for each bat x 600 = 14400 x 365 days (1 year) =5 256 000 - quite a lot?



I get runtime error 3265 -item not found in collection at this stage:
temp = temp & CStr(!State)


heres what Ive changed so far:

Private Sub Command11_Click()


Dim parray(3) As String
Dim udays() As Variant
Dim bats As Long
Dim i As Long
Dim temptime As Date
Dim temp As String
Dim batsout As Long
Dim batsin As Long
Dim db As DAO.database
Dim rstemp As DAO.Recordset
Dim rswrite As DAO.Recordset

Set db = CurrentDb
Set rstemp = db.OpenRecordset("SELECT [EventTime] " & _
"FROM Events ORDER BY [EventTicks]", dbopendynaset)


Set rswrite = CurrentDb.OpenRecordset("analysistable", dbopendynaset)

parray(0) = "0101"
parray(1) = "1010"
parray(2) = "0110"
parray(3) = "1001"

rstemp.MoveLast
rstemp.MoveFirst

udays() = rstemp.GetRows(rstemp.RecordCount)

rstemp.Close
Set rstemp = Nothing

For i = 0 To UBound(udays, 2) 'loop a table for every unique date

Set rstemp = db.OpenRecordset("SELECT [EventTime] " & _
"FROM EVENTS ORDER BY [EventTicks]", dbopendynaset)


rstemp.MoveLast
rstemp.MoveFirst
temptime = rstemp!EventTime

With rstemp



Do While Not .EOF

temp = temp & CStr(!State)

If Len(temp) = 4 Then
For j = 0 To UBound(parray) 'this loop type may need to change
If temp = parray(j) Then
Select Case temp
Case Is = parray(0) 'BAT FLEW OUT
batsout = batsout + 1
Case Is = parray(1) 'BAT FLEW OUT
batsout = batsout + 1
Case Is = parray(2) 'BAT FLEW IN
batsout = batsin + 1
Case Is = parray(3) 'BAT FLEW IN
batsout = batsin + 1
End Select
Exit For
End If
Next j
temp = ""
End If

.MoveNext
Loop
End With

bats = batsin - batsout

With rswrite
.AddNew
!datefield = temptime
!IncreaseOfBatsInRoostForDay = bats 'net change (in roost) from prev day
.Update
End With

rstemp.Close
Set rstemp = Nothing
bats = 0
batsin = 0
batsout = 0

Next i 'DO IT AGAIN

rswrite.Close
db.Close

Set rswrite = Nothing
Set db = Nothing
End Sub

 

[cmp20]

hi, it is an event logger; i.e. it logs a bat transit every time it goes in or out and then rests until the next event BUT... we have to use two horizontally parallel beams in order to determine direction, so for each transit, a bat will break one and then the other beam. To complicate things further, the database records when each beam is broken (state 1) and when it becomes free again (state 0) -thus the 4 records for each transit.

 

[the_net_2.0]

5 million records? YES, that's a lot. if you run code on that, the program will freeze, but the code will still run. when the program freezes like that due to long execution, vba has a tendancy sometimes to loop the code that is running forever and not stop. other things can happen too...thus I mention a breakpoint at regular intervals to reguvenate the memory.

I get runtime error 3265 -item not found in collection at this stage:
temp = temp & CStr(!State)

Please keep in mind that my code is not perfects (whos ever is? ). obviously it wasn't this time either, but I wrote it on the forum and did not test-run it. Here is the alternative, which should be bug free, assuming it is congruent with your objects:

Private Sub Command11_Click()


Dim parray(3) As String
Dim udays() As Variant
Dim bats As Long
Dim i As Long
Dim temptime As Date
Dim temp As String
Dim batsout As Long
Dim batsin As Long
Dim db As DAO.Database
Dim rstemp As DAO.Recordset
Dim rswrite As DAO.Recordset

Set db = CurrentDb
Set rstemp = db.OpenRecordset("SELECT DISTINCT [EventTime] " & _
                              "FROM Events ORDER BY [EventTicks]", dbOpenDynaset) [COLOR="Red"]'DISTINCT RECS NEEDED[/COLOR]
            
                             
Set rswrite = db.OpenRecordset("analysistable", dbOpenDynaset)

parray(0) = "0101"
parray(1) = "1010"
parray(2) = "0110"
parray(3) = "1001"

rstemp.MoveLast
rstemp.MoveFirst
   temp = "" [COLOR="Red"]'ALTERNATE CODE[/COLOR]

udays() = rstemp.GetRows(rstemp.RecordCount)

rstemp.Close
Set rstemp = Nothing

For i = 0 To UBound(udays, 2) 'loop a table for every unique date

Set rstemp = db.OpenRecordset("SELECT [State] " & _
                              "FROM EVENTS " & _
                              "WHERE [EventTime] = #" & udays(0, i) & "#" & _
                              " ORDER BY [EventTicks] ASC", dbOpenDynaset) [COLOR="Red"]'THIS FIXES YOUR ERROR[/COLOR]
                              

   rstemp.MoveLast
   rstemp.MoveFirst
      temptime = rstemp!EventTime

 With rstemp



   Do While Not .EOF

temp = temp & CStr(!State)

         If Len(temp) = 4 Then
            For j = 0 To UBound(parray) 'this loop type may need to change
               If temp = parray(j) Then
                  Select Case temp
                     Case Is = parray(0) 'BAT FLEW OUT
                        batsout = batsout + 1
                     Case Is = parray(1) 'BAT FLEW OUT
                        batsout = batsout + 1
                     Case Is = parray(2) 'BAT FLEW IN
                        batsout = batsin + 1
                     Case Is = parray(3) 'BAT FLEW IN
                        batsout = batsin + 1
                  End Select
                           Exit For
               End If
            Next j
                     temp = ""
         End If

      .MoveNext
   Loop
End With

bats = batsin - batsout

   With rswrite
      .AddNew
      !datefield = temptime
      !IncreaseOfBatsInRoostForDay = bats 'net change (in roost) from prev day
      .Update
   End With

         rstemp.Close
         Set rstemp = Nothing
            bats = 0
            batsin = 0
            batsout = 0

msgbox "Loop number " & cstr(i + 1) & " of " & _
       cstr(UBound(udays, 2) + 1) & _
       " completed.  Press OK to run next loop..." [COLOR="Red"]'EXAMPLE BREAK POINT TO CONSERVE MEMORY[/COLOR]

Next i 'DO IT AGAIN

rswrite.Close
db.Close

Set rswrite = Nothing
Set db = Nothing
End Sub

the recordset that uses the array udays() as criteria is assuming that the EventTime field has dates in it. The first post shows that. Dates always need to be surrounded by #'s in criteria code.

 

[ChipperT]

hi, it is an event logger; i.e. it logs a bat transit every time it goes in or out and then rests until the next event BUT... we have to use two horizontally parallel beams in order to determine direction, so for each transit, a bat will break one and then the other beam. To complicate things further, the database records when each beam is broken (state 1) and when it becomes free again (state 0) -thus the 4 records for each transit.

Hi,
I have been following this thread with some interest because my son is a zoologist and did his master's thesis on a study of nesting habits of the lesser myotis (little brown-legged bat) and the effects of riparian cutting by a timber company. Anyway, just to make sure I have the concept right before I cogitate on this for a possible solution:

If my understanding is correct, you need to consider the signals in batches of three couplets:

10
00
01
means a bat has passed by (in this case from left to right)

01
00
10
means a bat has passed by (in this case from right to left)

10
11
01
means an insect has flown by (direction does not matter since you would first examine the middle couplet for 11. If true, throw it out).

Is this a correct assumption?

 

[ChipperT]

And more questions:

1. What are the chances of "noise" - that is, a beam fails to trigger, a falling leaf triggers only one bean, a bat, flying at an extreme angle, triggers one beam and passes over the second, etc?

2. Are you required to maintain and consider the data in the current database or can you redesign and start from scratch?

 

[cmp20]

And more questions:

1. What are the chances of "noise" - that is, a beam fails to trigger, a falling leaf triggers only one bean, a bat, flying at an extreme angle, triggers one beam and passes over the second, etc?

2. Are you required to maintain and consider the data in the current database or can you redesign and start from scratch?

hi, I said beam to keep it simple, its actually an array of beams 30cm high covering entire roost entrance -with only 1cm vertical seperation between beams!
noise will be from birds (can eliminate since daytime only) and at 1cm apart there is no chance of anything relevant missing a beam. noise also from insects -can be eliminated by beam break pattern -see below -also small leaves.
other leaves would depend on the location -many roosts are in old attics / attached to buildings and leaves would not pose threat there.

let us say that beam one is left and beam 2 is right

DATA:
beam one =0,
next record, beam 2 = 0 (state 0 followed by next record state 0) = empty =
this is the start or stop state

next record beam one = 1
next record beam two = 1
next record beam one = 0
next record beam two = 0
these four records indicate that an object large enough to trigger both beams passed from left to right

note: the last two records were 0,0, thus beginning and end of 4 record set

next record beam two = 1
next record beam two = 0
next record beam one = 1
next record beam one = 0
these four records indicate that an object too small to trigger both beams simultaneously (insect?) passed from right to left -note small objects are rare, bats are by far the common thing in the beams.

this is where my reasoning breaks down because although the physical state of the beams is now 0,0 the last two records were not 0,0

Seems I am a bit stuck in the basic algorithm here...

I cannot start from scratch since the method of getting the data directly into the database is way over my head, I just followed installation instructions and required drivers.

There is another method of getting the raw data: a csv file. This could then be imported into a scratch database but again, the records are sequential....
so it would probaly not be any more use...

 

[cmp20]

" & _
"FROM Events ORDER BY [EventTicks]", dbOpenDynaset) 'DISTINCT RECS NEEDED

Hi thenet2,

DISTINCT may be needed but it is causing an error:
run-time error 3093
order by clause ([EventTicks]) conflicts with DISTINCT

i found this: http://msdn.microsoft.com/en-us/library/bb223434(v=office.12).aspx

I modified your code to
... SELECT DISTINCT [EventTime], [EventTicks] " & ...
and it seems to work but now stalls at:
rstemp.MoveLast with runtime error 3021, no current record


im a bit stuck here..
thanks
cm

 

[ChipperT]

hi, I said beam to keep it simple, its actually an array of beams 30cm high covering entire roost entrance -with only 1cm vertical seperation between beams!
noise will be from birds (can eliminate since daytime only) and at 1cm apart there is no chance of anything relevant missing a beam. noise also from insects -can be eliminated by beam break pattern -see below -also small leaves.
other leaves would depend on the location -many roosts are in old attics / attached to buildings and leaves would not pose threat there.

let us say that beam one is left and beam 2 is right

DATA:
beam one =0,
next record, beam 2 = 0 (state 0 followed by next record state 0) = empty =
this is the start or stop state

next record beam one = 1
next record beam two = 1
next record beam one = 0
next record beam two = 0
these four records indicate that an object large enough to trigger both beams passed from left to right

note: the last two records were 0,0, thus beginning and end of 4 record set

next record beam two = 1
next record beam two = 0
next record beam one = 1
next record beam one = 0
these four records indicate that an object too small to trigger both beams simultaneously (insect?) passed from right to left -note small objects are rare, bats are by far the common thing in the beams.

this is where my reasoning breaks down because although the physical state of the beams is now 0,0 the last two records were not 0,0

Seems I am a bit stuck in the basic algorithm here...

I cannot start from scratch since the method of getting the data directly into the database is way over my head, I just followed installation instructions and required drivers.

There is another method of getting the raw data: a csv file. This could then be imported into a scratch database but again, the records are sequential....
so it would probaly not be any more use...

Ok, I see that I got the bug one wrong but I am more confused with your explanation. Shouldn't the data reflect the movements as such:

Assumption: beam 1 is left, beam 2 is right
Action: bat is flying left to right (beginning with before the bat enters the recording zone and ending after the bat has cleared the zone).
Data sets:
00
10
11
01
00

Bat flying right to left
00
01
11
10
00

Bug, etc breaking the beams

00
01
00
10
00

"noise" (falling leaf, etc only breaking one beam or both beams)

00
01
00
00

or
00
11
00
00

Is that now an accurate picture of the data to be received?

 

[cmp20]

Ok, I see that I got the bug one wrong but I am more confused with your explanation. Shouldn't the data reflect the movements as such:
...
...
...
Is that now an accurate picture of the data to be received?

OK,
I see what you are doing now, you have the basic concept correct but you miss the fact that there is only one pin and one state per record, not two:
ID EventTicks EventTime IPAddress Pin State

 

[ChipperT]

o, I see what you ar doing now, you have the concept correct now but you miss the fact that there is only one state per record, not two:
ID EventTicks EventTime IPAddress Pin State

Actually, I knew that. But I was describing the sets of data. The data is actually only meaningful if you have a defined couplet (pin 1 state, pin 2 state), organized into sets that describe the action you are measuring. That is what I was doing.

So, if my description is correct, what I would do is interate through the data in couplets (always in pin 1, pin 2 sequence) and look for sets of either
00
10
11
01
00

or

00
01
11
10
00

All other sets could be ignored as "noise". You could then create a table of these sets that looks something like this:

tblDirection (this is a static lookup table to describe the data)
idDirectionPK descDirection
------------ -------------
0 right to left
1 left to right

tblBatData (this is the table that will hold your filtered data)
idBatDataPK EventTime IPAddress idDirectionFK
----------- ---------- ---------- ------------

So, when you find a set that matches either left to right, or right to left, you update tblBatData with the EventTime, IPAddress and either 0 or 1. This gives you a coherent set of data, minus all noise and anomalies, to base a decision on when counting your bats. Of course, your accuracy is never going to be 100% since you cannot take into account bats that leave and never come back, etc. But I think it would get you to where you want to be.

 

[the_net_2.0]

cmp,

check out the attachment. I went ahead and did a sample loop for you. note that I changed the date field to "short date". other wise, I don't think distincts would be possible. vba said it wasn't, anyway. I also fixed another syntax error in the code that I was still missing. sorry about that! there's one procedure in the module in the db window. run that and you'll see that on 11/12/10, -2 bats were the net catch for that day.

 

[cmp20]

thanks ChipperT, yes it looks like that would work
pints on me next time I'm in Dingle -or Doolin.

I'm gonna have a look at theNet2's database now, cheers.

 

[cmp20]

thanks TheNet2,
gonna have a go at your DB now

 

[ChipperT]

thanks ChipperT, yes it looks like that would work
pints on me next time I'm in Dingle -or Doolin.

I'm gonna have a look at theNet2's database now, cheers.

Actually, this would be pretty easy if you were using SQL Server or another db that has stored procedures along with triggers. Well, good luck. If you are ever in Dingle, raise a pint for me. I probably will not be back for a few years.

 

[cmp20]

cmp,

check out the attachment. I went ahead and did a sample loop for you. note that I changed the date field to "short date". other wise, I don't think distincts would be possible. vba said it wasn't, anyway. I also fixed another syntax error in the code that I was still missing. sorry about that! there's one procedure in the module in the db window. run that and you'll see that on 11/12/10, -2 bats were the net catch for that day.

sorry, this is probably a really stupid question: how should I run the module? the run command is greyed out in the toolbar. I couldnt call it in a macro either or run it from a cmd button. Do I need to give any arguements?
thanks,

 

[the_net_2.0]

put your cursor inside the 'sub' somewhere and press f5. e.g. - click on 'sub f()' and press f5.

 

[cmp20]

put your cursor inside the 'sub' somewhere and press f5. e.g. - click on 'sub f()' and press f5.

tried that: still getting error 3021 no current record.
fails at: rstemp.MoveLast

 

[the_net_2.0]

It runs fine for me.

what version are you using?

does it fail on the FIRST .movelast or the SECOND? there's 2 in the proc. obviously what's happening is that the recordset that it's opening has 0 records, which is why it fails. the query that's opening the rstemp is returning 0 records.

if it is failing on attempt number 2, the udays() array is also involved, so that might also be causing the problem. there is something different between the objects layed out in my sample and your original that you're running it in.

if you're trying to run this in the actual db that I uploaded here, there's really no reason for it not working, as I am running the same proc. you would be, and in the same file that you would be.