Find record between records

[veeRob]

To give you a little background, I am a VB6 programmer but have little experience with Microsoft Access. I have a VB6 program that does a little crunching of my database to return some info. For example, my database looks like this:

ID Action
0 state=0
1 alan
2 barry
3 state=1
4 alan
5 state=0
6 alan
7 state=1
8 alan

My program's crunching needs to count all of the iterations of "alan" when the state=0. That means in this sample that "alan" shows up twice when state is 0, not 4 times. I have not figured out a way to do this in my SQL statement, so if there is a way to do that, I'd love to hear it.

If there is not, I'd like to use some sort of stored procedure inside Access so I don't need to duplicate this code in every VB6 program I use. I toyed around with Access and was able to create a module and recreate this crunching in that module, but I can't figure out how to call that function from a VB6 application.

Can anyone please clear up how I can store this function in Access and use it from VB6, or better yet explain a way I can do this processing with pure SQL.

 

[Remou]

Perhaps something like:
[tt]SELECT tblTable.ID, tblTable.Action,
(SELECT Action
FROM tblTable A
WHERE a.id=tblTable.id+1) AS NextRec
FROM tblTable
WHERE (((tblTable.Action)="state=0") AND
(((SELECT Action
FROM tblTable A
WHERE a.id=tblTable.id+1))="alan"));[/tt]

 

[veeRob]

Without knowing 100% what that SQL query does exactly, I'd venture to guess that this won't work. Unfortunately I forgot to mention that there could be many entries between the state=0 and the person's name, so sample records could look like this:

ID Action
0 state=0
1 carl
2 barry
3 alan
4 state=1

As you can see here, all three people were seen between state=0 and state=1. I believe in your example, it would only grab the person in the entry directly following a state=0 entry. Please correct me if I'm wrong.

I need the SQL query to catch all three people in this case, as they all were seen between states.

 

[ddiamond]

Here is the pseudo sql:
Find all 'alan' records where there exists a 'state=0' before 'alan' and there does not exist any other 'state' records between 'alan' and 'state=0'.

Actual sql:

select a.id, a.action
from myTable a
where a.action = 'alan'
  and exists (
    select 1 from myTable b
    where b.action = 'state=0'
      and b.id < a.id
      and not exists (
        select 1 from myTable c
        where c.action like 'state=*'
          and b.id < c.id
          and c.id < a.id)

 

[traingamer]

Your problem stems from poor database design.

If, at time of entry, the database / system knows what the state is, then state should be included in the 'Alan' record as another field, removing this exercise completely.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill