Where to start?

[MikeB]

I've played with Access before and have recently written a little set
of tables for my daughter's school chess club. I now like to expand
this to do some rating of the players. For this I need some
calculations performed on each game each player played. I think this
is best accomplished in a program, yes?

Does anyone have a skeleton VBA program that shows the necessary
commands to open an access table and access the records in it
sequentially? Some commands to then access other records would also be
very helpful.

Also, if I want to store the rankings in a table, do I have to pre-
create the table (or column) or can I add a table on the fly? If the
table already exists, can I delete it through the program or do I need
to run some SQL to delete/create the rankings table?

Thanks,

 

[Pat Hartman]

Here's a piece of code that reads a recordset and renumbers its members. It
should include all the functionality you need to proceed. However, some
things are better done with queries rather than code loops.

The query - qCountQuoteVersions - takes a parameter "HeaderID" which is
passed to the function when it is called.


Public Function RenumberQuotes(HeaderID As Long) As Integer
On Error GoTo Err_PROC

Dim db As DAO.Database
Dim rsDAO As DAO.Recordset
Dim qdDAO As DAO.QueryDef
Dim NewQuoteNumber As Integer
Dim OldQuoteNumber As Integer
Dim QuoteCount As Integer

Set db = CurrentDb()
'get count of quotes
Set qdDAO = db.QueryDefs!qCountQuoteVersions
qdDAO.Parameters![EnterVariableDataHeaderID] = HeaderID
Set rsDAO = qdDAO.OpenRecordset
If rsDAO.EOF = True Then
RenumberQuotes = 0
Exit Function
Else
QuoteCount = rsDAO!CountOfQuoteVersion
RenumberQuotes = QuoteCount 'set return value = # of quotes
End If

'find highest quote number
Set qdDAO = db.QueryDefs!qMergeQuoteFields
qdDAO.Parameters![EnterVariableDataHeaderID] = HeaderID
Set rsDAO = qdDAO.OpenRecordset
rsDAO.MoveLast

If rsDAO!QuoteVersion = QuoteCount Then
Exit Function 'no renumber required
End If

rsDAO.MoveFirst
NewQuoteNumber = 0
Do While rsDAO.EOF = False
If rsDAO!QuoteVersion = OldQuoteNumber Then
Else
NewQuoteNumber = NewQuoteNumber + 1
OldQuoteNumber = rsDAO!QuoteVersion
End If

rsDAO.Edit
rsDAO!QuoteVersion = NewQuoteNumber
rsDAO.Update

rsDAO.MoveNext
Loop

Exit_PROC:
Exit Function
Err_PROC:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_PROC
End Function

 

[MikeB]

Here's a piece of code that reads a recordset and renumbers its members. It
should include all the functionality you need to proceed. However, some
things are better done with queries rather than code loops.

The query - qCountQuoteVersions - takes a parameter "HeaderID" which is
passed to the function when it is called.

Public Function RenumberQuotes(HeaderID As Long) As Integer
On Error GoTo Err_PROC

Dim db As DAO.Database
Dim rsDAO As DAO.Recordset
Dim qdDAO As DAO.QueryDef
Dim NewQuoteNumber As Integer
Dim OldQuoteNumber As Integer
Dim QuoteCount As Integer

Thanks for the sample, it should get me on my way. Just a few
questions.

1. I see you use DAO. There is also mention of some access method
called ADO. I've been looking, but can't find a simple description of
which is which and which is the most preferable (newest?) method to
use.


Actually, the other questions can wait until I've studied your example
(and the advice to use queries) more thoroughly.

Thanks much.

 

[Ken Sheridan]

In a relational database ranking would normally be computed by means of a
query rather than by code. The technique is to use a subquery to return the
count of rows where the score values are greater than or equal to the current
row. In a very simple example one might first sum the scores of each player
in all games:

SELECT PlayerID, SUM(Score) AS TotalScore
FROM Games
GROUP BY PlayerID;

Then base another query on this query to compute the ranking of each player:

SELECT PlayerID,
(SELECT COUNT(*)
FROM qryTotalScores AS TS2
WHERE TS2.TotalScore >= TS1.TotalScore)
AS Rank
FROM qryTotalScores AS TS1;

The ranks would not normally be stored as values in a column in a table but
computed on the fly as above. Storing them would introduce redundancy and
the consequent risk of inconsistencies in the data. If it is necessary to
know the differing ranks of players over a period of time the second query
above, and its subquery, can always be restricted by date parameters to give
the ranks at any point in time.

Ken Sheridan
Stafford, England

 

[Douglas J. Steele]

DAO (Data Access Objects) was developed specifically for Jet (the database
engine Access uses), although it can work with other data sources as well.
ADO (ActiveX Data Objects) is more recent, and was intended to be a more
generic approach. However, whenever you make something generic, you create
layers of abstraction which can cause performance issues. For this reason,
if you're strictly dealing with a Jet database, I'd say it's better to use
DAO. Note that ADO is no longer an active technology, as it was essentially
replaced with ADO.Net (quite a different thing), which doesn't work with
Access.

 

[Pat Hartman]

True, ranking can be done with a query (as long as the recordset is not too
large) and if I had thought about it, I probably would have done this with
an update query since the number of records being resequenced is rarely more
than 3. This is a user controlled record sequencing issue so I prefer to
store the sequence number once when the sequence is updated rather than
every time the data is accessed. The sequence rarely changes but they need
the option.

 

[MikeB]

True, ranking can be done with a query (as long as the recordset is not too
large) and if I had thought about it, I probably would have done this with
an update query since the number of records being resequenced is rarely more
than 3. This is a user controlled record sequencing issue so I prefer to
store the sequence number once when the sequence is updated rather than
every time the data is accessed. The sequence rarely changes but they need
the option.

I said "ranking" when I should have said "rating."

Chess ratings are calculated and each player gets a rating. Each
opponent's rating is used in deriving a player's rating, hence it
wouldn't be (IMO) feasible to not store the ratings - if I wanted to
produce a list of ratings, I would have to recalculate all the
players' ratings, and each time a player plays a game and his/her
rating needs to be updated, I'd have to recalculate all his/her
opponents' (and their opponents) ratings.

The most common rating system used for chess ratings is the elo system
(http://en.wikipedia.org/wiki/Elo_rating_system)

 

[Jamie Collins]

Note that ADO is no longer an active technology, as it was essentially
replaced with ADO.Net (quite a different thing), which doesn't work with
Access.

Just to clarify your point there: ADO.NET, part of the .NET framework,
can indeed work with _Jet_ using System.Data.OleDb and Jet's OLE DB
providers.

What you meant, of course, is that ADO.NET cannot be used directly in
VBA, hence ADO.NET cannot be used directly in the _Access user
interface_ (I'm assuming that both indirection via COM interop and
Visual Studio Tool for Office extensions for Access are out of scope
as regards this discussion).

Jamie.

--