count consecutive wins per reg key

[skptigger]

I need to count the number of consecutive wins(Fin Pos) per reg key from this data. Is it possible to do this with Access? I'm fairly new to Access queries.

Out of the larger group of data shown I would want:

"Reg Key" "Name"               "Consecutive Wins"
578664      CHANT'S ALIBHAI    10
614060      CHARGER BAR             3

"Reg Key" "Name"            "Index No" "Fin Pos"
578664      CHANT'S ALIBHAI    84374    7
578664      CHANT'S ALIBHAI    84802    4
578664      CHANT'S ALIBHAI    85070    1
578664      CHANT'S ALIBHAI    85734    1
578664      CHANT'S ALIBHAI    86173    1
578664      CHANT'S ALIBHAI    86381    1
578664      CHANT'S ALIBHAI    86858    1
578664      CHANT'S ALIBHAI    87066    1
578664      CHANT'S ALIBHAI    87679    1
578664      CHANT'S ALIBHAI    88592    1
578664      CHANT'S ALIBHAI    88771    1
578664      CHANT'S ALIBHAI    89058    1
578664      CHANT'S ALIBHAI    89219    2
578664      CHANT'S ALIBHAI    89507    1
578664      CHANT'S ALIBHAI    89926    8
578664      CHANT'S ALIBHAI    94420    6
614060      CHARGER BAR            88785    3
614060      CHARGER BAR            88878    6
614060      CHARGER BAR            89149    1
614060      CHARGER BAR            92813    4
614060      CHARGER BAR            93436    1
614060      CHARGER BAR            93894    1
614060      CHARGER BAR            94226    1

[Seth Schrock]

Most things are possible, but you don't explain how you came up with the result that you are looking for. I personally see no correlation between the results you are wanting and the larger data group.

**Edit**
I think that I might see it now. The field Fin Pos is Finish Position and you are looking for consecutive records where the finish position is 1. Is that correct? Do you have a primary key field? Also, what do you want to happen if you have two groups of consecutive wins? For example, what if the record

614060     CHARGER BAR     88878    6

were changed to a win so that you would end up with

614060     CHARGER BAR     88785    3
614060     CHARGER BAR     88878    1
614060     CHARGER BAR     89149    1
614060     CHARGER BAR     92813    4
614060     CHARGER BAR     93436    1
614060     CHARGER BAR     93894    1
614060     CHARGER BAR     94226    1

Would you want the result set to be

578664     CHANT'S ALIBHAI    10
614060     CHARGER BAR        3
614060     CHARGER BAR        2

[skptigger]

Yes that is right. I want to count how many consecutive finish positions of 1 for each horse. the number field is a number representing the horse(ex: Charger Bar's number is 614060) If there is more than one within a horse number then I would want to count both instances like you showed in your post.

I'm using Access 2010.

[zmbd]

Seth,
Go back to the origninal post data, second block.
OP is counting records from 4 thru 13, because at 14 [CHANT'S ALIBHAI] "place" and then doesn't have a consecutive following that point.
Same thing with [CHARGER BAR], where OP is only counting records 22 thru 24.
If there is a subsequent series of wins then that is also usually recorded... this way they keep track of winning streaks.

Standard Horse race thing - watched my Mom's boss do this by hand in a little brown book :).
(sorry, recognize the names - however, I think these are very old stats, Chant's Alibhai raced in the mid to late '70s - yes? Breading in '80s or '90s - Seems I remember going to the races in DelMar and hearing the name in relation to a horse - been almost 30 years so I might be mis-remembering.)
Never tried to do a DB on it.

[zmbd]

Looks Like I cross posted with skptigger

[Seth Schrock]

Yep, I got that now Z. Thank-you for the formatting too. Definitely one of those times where I wish I was a moderator and could do it myself.

I'm thinking that this is going to require a subquery possibly in a GROUP BY clause, but I don't have anything concrete in my mind yet. I'll keep working at it, though. Is there any date field or primary key value that would help the database know the order of the races? Or is it just the order of the entry?

[skptigger]

There is a race date field in the data I have,but I included the index # in this post. It is unique to each race and can be used to put the races in order by sorting on the it. If the race date is preferrable I can add it.

[Rabbit]

There are two ways to do this. The VBA way and the SQL way.

The VBA way is to loop through an ordered recordset while counting and looking for a break in the wins before restarting the count. And after each break, you will have to compare it to the prior max. Once, you reach the end of a group, you output the max for that group.

The SQL way is to join the table to itself to get the records after it that are not first place for each record that is first place. Take the MIN() of the end range for each one. Then do a COUNT() for each end range. And finally get the MAX() for each group.

[skptigger]

Ok, I'm new to this forum and new to VBA. Is it possible/acceptable to get an example of the code for these?
@Rabbit

[Rabbit]

It's not the policy to do the work for poster on this forum but since this is a little bit complex, I don't mind posting some pseudocode.

If you don't know VBA, then you'll want to learn the basics so you can translate the psuedocode into VBA code.

priorGroup = ""
 
For Each row In ordered recordset
   If group != priorGroup
      output group and MaxConsecutiveWins
      priorGroup = group
      MaxConsecutiveWins = 0
      ConsecutiveWins = 0
   End If
 
   If place = 1 Then
      ConsecutiveWins += 1
   Else
      If ConsecutiveWins > MaxConsecutiveWins
         MaxConsecutiveWins = ConsecutiveWins 
         ConsecutiveWins = 0
      End If
   End If
 

As for the SQL version, there's really no way to do psuedocode for that but here's a basic example implemented for SQL Server that you can translate to Access SQL.

declare @t table(groupField char(1), orderField int, placeField int)
insert into @t
select 'A', 1, 2 union all
select 'A', 2, 1 union all
select 'A', 3, 1 union all
select 'A', 4, 1 union all
select 'A', 5, 1 union all
select 'A', 6, 1 union all
select 'A', 7, 3 union all
select 'A', 8, 1 union all
select 'A', 9, 1 union all
select 'A', 10, 1 union all
select 'B', 12, 2 union all
select 'B', 13, 1
 
 
SELECT
    x.groupField,
    MAX(x.ConsecutiveWins) AS maxConsecutiveWins
 
FROM
    (
        SELECT 
            t.groupField,
            COUNT(*) AS ConsecutiveWins
 
        FROM 
            (
                SELECT 
                    t1.groupField,
                    t1.orderField,
                    MIN(
                        CASE 
                            WHEN t2.orderField IS NULL
                                THEN -1
                            ELSE t2.orderField
                        END
                    ) AS EndRange
 
                FROM 
                    @t AS t1
 
                    LEFT JOIN @t AS t2 ON
                        t1.groupField = t2.groupField AND
                        t1.orderField < t2.orderField AND
                        t2.placeField <> 1
 
                WHERE
                    t1.placeField = 1
 
                GROUP BY
                    t1.groupField,
                    t1.orderField
            ) t
 
        GROUP BY
            t.groupField,
            t.EndRange
    ) x
 
GROUP BY x.groupField

[skptigger]

Don't worry it's not for work. It is just something that I wanted to do on my own. Thanks for the examples.

[Rabbit]

I didn't mean work as in a job. I only meant the general sense of work. If I just post fully working code, there's no incentive to understand how and why it works. And if you don't understand the solution, you won't be able to adapt it if the circumstances change a little. It's the whole teach a person to fish thing.

Let us know how you get along with transforming the examples to fit your situation.