Stumped for solution

[Hugh]

I have a query that returns a handicap score for each of the 16 players in
my league for each week of play. Based upon that handicap score I now need
to assign points to each player starting from the lowest score to the
highest, the lowest receiving 16 pts, next lowest 15 pts, etc. I also have
to keep a running total of each players points from week to week which I
thought I would store in a table with an update query.

We play an 18 week season but only 16 weeks of points are counted for any
one players total points. Any points won after the 16 week limit can no
longer be added to the total points of that player. If a player does not
show in any given week he is given a zero (0) score which does not count
toward his 16 weeks of active scoring.

I am working in Access 2000 and this is beyond my novice knowledge. The
query by which I acquire the handicap score has these fields which are
extracted from two other queries:
Players
Date
Score
Handicap
HdcpScore

Any genuine help would be greatly appreciated and serve to increase my
knowledge in this wonderful program.

Hugh

 

[PC Datasheet]

Here's an outline of what you need to do ---

1. Sort your query that returns a handicap score for each of the 16 players
descending
2. Create a recordset from the query
3. Use a for next loop to assign points:
For I = 16 to 1 Step -1
If Player has less than 17 weeks of play then
Player's points = I
End If
Recordset.MoveNext
Next

 

[Hugh]

Thank you "PC" for your help, unfortunately, I have very limited knowledge
using Access and am not familar with how to go about doing what you suggest.
How do I create a recordset from the query and where do I put the for/next
loop? The query is already set to sort in the manner you stated. I love this
program and am trying to learn it as fast as I can. Can you suggest any
books or tutorials that would aid me in my quest? Thanks again.

Hugh

 

[PC Datasheet]

What you want to do requires some VBA programming. There's no way to get
around that.

Dim Db As DAO.Database
Dim Rst As DAO.Recordset
Set Db = CurrentDb()
Set Rst = Db.OpenRecordset("NameOfYourQuery",dbOpenDynaset)
For I = 16 to 1 Step -1
If Player has less than 17 weeks of play then
Player's points = I
End If
Recordset.MoveNext
Next
Rst.Close
Set Rst = Nothing
Set Db = Nothing

You need to work out the logic and code in the For Next loop.

The code goes in the Click event of a command button on a form.

MUST HAVE books:
Using Microsoft AccessXXX, Roger Jennings, Que Corp - Reference for nuts and
bolts of Access
Beginning AccessXXX VBA, Sussman and Smith, Wrox - VBA Programming
AccessXXX Developer's Handbook, Getz et al, Sybex - Intermediate to Advanced
Reference

I am in business to provide customers with a resource for help with Access.
So another option for you would be for you to have me build the database for
you. My fees are very reasonable. I have seen many times where a customer
hired me to build a database for them and afterwards when the customer had a
database he was familiar with, interested in it and studied it, the customer
took a large jump up the Access learning curve.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com