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]

example code

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

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.

[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.