|
Having a suite of integers, not necessary continuous, but without repetition, we want to know what is the maximum length of continuous sequence of integers present in the suite. Here the ordered (this is not necessary) suite:
data={1, 2, 5, 6, 7, 8, 9, 11, 13, 14, 18, 22, 23}
then, the greatest continuous sequence is 5, 6, 7, 8, 9 for a "length" of 5.
We can easily get this value if you note that a given value, minus its rank, will give a constant number for a given continuous
sequence:
data= {1, 2, 5, 6, 7, 8, 9, 11, 13, 14, 18, 22, 23}
rank= {1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12, 13}
minus={0, 0, 2, 2, 2, 2, 2, 3, 4, 4, 7, 10, 10}
so, we just have to GroupBy those differences and Count their occurence:
difference occurs
0, 2
2, 5
3, 1
4, 2
7, 1
10, 1
If the table name is Sequences and if the field is Sample, create a new query, drag Sample in the grid; in another column, write:
[Sample]-DCount("*", "Sequences", "Sample<=" & [Sample] )
Push the summation button on the toolbar, a new line in the grid will appear (Total). Change the GroupBy to Count for the field Sample, but keep it,GroupBy, for the computed expression. If you take a look at the data, at this moment, you should see two columns, the number of occurences and the
difference (value minus its rank). The SQL statement should be something like:
SELECT Count(Sequences.Sample) AS CountOfSample,
[Sample]-DCount("*","Sequences","Sample<=" & [Sample]) AS Expr1
FROM Sequences
GROUP BY [Sample]-DCount("*","Sequences","Sample<=" & [Sample]);
Save the query, Q1. It is then easy to retreive its maximum from column CountOfSample, here, the value 5 in our example.
Here is another example: PlayersGames is a table that list two fields: PlayerID, GameNo. You want to know, for each player, what is its longest
sequence of un-interruped play (i.e.: the longest sequence without missing a game).
Make a new query, on tble PlayersGames. Push the summation button on the toolbar to make it a total query. Drag the PlayerID field, keep the GroupBy
(we want per Player). Drag GameNo, change GroupBy to Count. Make a third computed column:
Occurence: [GameNo]-DCount("*", "PlayersGames", _
"PlayerID=" & [PlayerID] & " AND GameNo<=" & [GameNo] )
and you will keep the group by. Save the query, PGQ1.
Make a new query, bring PGQ1. Make it a total query. Bring PlayerID, keep its GroupBy. Bring Occurence, change its GroupBy to Max. You have then the
desired result.
Can you do the same, now, in VBA with a recordset-thing? as easily?
|
