ranking players for a cross country team 1

[statman]

How do you set up a query that will automatically rank the players after you have sorted them? Their ranking is then used to add up team score. Have tried auto number but wont work due to not being able to start at zero when i want [sig][/sig]

 

[MichaelRed]

statman,

O.K. This may (will) appear to be a bit weird.

There is no real/direct way to do this. I could ramble on about the why ...

You can (sort of) get the effect with a little bit of trickery - but even then there are some cautions.

Look at the "Stuff" (code?) below:

Public Function basIncrInt(Dummy As Variant) As Integer

'SELECT tblNewPubls.PubCd8, tblNewPubls.Name,
'basIncrInt([ActNum]) AS Rank INTO tblTestIncrINt
'FROM tblNewPubls;


    Static LastVal
    
    If (LastVal < 32767) Then
        LastVal = LastVal + 1
     Else
        LastVal = 0
    End If
    basIncrInt = LastVal

End Function

The commented stuff at th top (Select ..._) is the SQL for a MAKE TABLE query. The query will return make the table tblTestIncrINt with the Number ranking - BUT the numbers will start at 1 only the first time you run the process for each time you start the database program!!!!

Your Make table query should be wahtever sorted information you need for the report, with the addition of the Rank field, which needs to call the function basIncrInt with some (ANY) field from the query.

The 'Weirdness' explained.

The number(s) start at one on the first call (query), because the static variable LastVal is not initalized, and the routine increments the zero (non-initalized) value and returns it to the calling program. BUT, since LastVal is [red]static[/red], it retains the value for the next time it is called, thus we get the incrementing value for the remainder of the calls. This is ALSO why the routine will not start at "1" if you call it again without shutting down the program (database).

On the other hand, you need to give the call to the routine a field value from the query's source. Why? The optimizer is "smart". If you call a routine without any arguments, it (the optimizer) assumes that the same value is returned each time the routine is called - therfore it uses the "samrts" to figure out that it need to call such a routine only once, get the (obviously) single value the function returns - and plug it into each place where the value is called!!!

As a 'minor' caution, this is intended for small data sets, so the return type of the function and LastVal are both set to Integer. For the Cross country race, I think 32,767 entrants is quite sufficient, however if you have other uses in mind you may need to modify the datatypes and the part of the code which resets lastval.

Also note, that to use this for Ms. Access Reports, you need to change the report record source to be the table generated by the query. Further, if this is just being run from the database window (or toolbar), the query will prompt you for the overwritting of the table for each execution - except the first.

Hopefully htis is not to confusing - if it is, please ask for additional expliniation. [sig]<p>MichaelRed<br><a href=mailto:mred@duvallgroup.com>mred@duvallgroup.com</a><br>There is never time to do it right but there is always time to do it over[/sig]