Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
ranking players for a cross country team 1
- Thread starter statman
- Start date
-
1
- #2
MichaelRed
Programmer
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:
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]
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:
Code:
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 FunctionThe 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]
- Status
Similar threads
