Stumped! Counting query based on 2 different tables

[TheWolfster]

We have two tables --Table A and Table B. In each table, there is a column titled "time" and a column titled "score." Each ID has a score for every single second (time). Therefore, each row reflects a specific time matched with a specific score per person (ID).

(The spacing gets formatted, but one can see the different columns)

e.g.:
Table A
ID Time Score
1 1 0
1 2 0


1 3 1
1 4 1
1 5 1
...
1 198 1
1 199 0
1 200 0
1 201 1
1 202 1
1 203 0
1 204 1
...
2 1 1
2 2 1
2 3 0
2 4 1
2 5 0
2 6 0
2 7 1
2 8 0
...

In contrast Table B only has ID and time values based on other criteria.

Table B

ID Time
1 3
1 201
...
2 1
2 4
...

We want access to use 'time' values from Table B as markers as a starting point to COUNT from Table A. Specifically, we want it to count the range of time (time_begin to time_end) where there are consecutive scores of '1' in Table A.

This would be what the end result would look like, with 3 columns: ID, Time_begin, and Time_end

Table C
ID Time_begin Time_end
1 3 5
1 201 202
...
2 1 2
2 4 4
...

Any help would be appreciated!

[weekend00]

you need to program some VBA code to scan the tables.

[NTC]

It is your table C that has me stumped in that I see no logical relationship to the score value in your example:

Table C
ID Time_begin Time_end
1 3 5
1 201 202
...
2 1 2
2 4 4
...

I see the 1-3, 1-201, 2-1, 2-4 came from table B. But the score you show i.e. 5 (for the 1-3) does not seem to correspond to anything that I see.

[weekend00]

NTC:

I see the 1-3, 1-201, 2-1, 2-4 came from table B. But the score you show i.e. 5 (for the 1-3) does not seem to correspond to anything that I see.

5 is the max time that has consequent score=1 from 1-3 of tableA
1 3 1
1 4 1
1 5 1
1 6 0

[weekend00]

Following code is tested:
I did not create a table C, instead, I add a field TimeEnd in tableB.

Public Function getTimeEnd()
'add a field timeEnd to tableB, the code will put end time in it
'if no score =1, timeEnd is 0
'ID,time, timeEnd are number fields.
'no matter if time is not consequent
    Dim rsA As DAO.Recordset, rsB As DAO.Recordset
    Dim curTime As Long 'max time with score 1
    Set rsA = CurrentDb.OpenRecordset("select * from tableA order by id,[time]")
    Set rsB = CurrentDb.OpenRecordset("select * from tableB order by id,[time]")
    Do While Not rsB.EOF
        curTime = rsB![Time]
        Do
            rsA.FindFirst "ID=" & rsB!ID & " and [time]=" & curTime & " and [score]=1"
            If rsA.NoMatch Then
                rsB.Edit
                rsB![timeEnd] = IIf(curTime = rsB![Time], 0, curTime - 1)
                rsB.Update
            End If
            curTime = curTime + 1
        Loop While Not rsA.NoMatch
        rsB.MoveNext
    Loop
    Set rsA = Nothing
    Set rsB = Nothing
End Function

[TheWolfster]

Excellent! Than you so much. It seems to work well (Access freezes, probably because of the hundreds of thousands of entries). I ended up killing it once and checking the table, and it in fact did update!

Now to just make sense of all this data!

+1

[weekend00]

Please create indexes on ID and time if you have not done so.

[TheWolfster]

I did do that, it just takes a very long time. Thanks again.