How to extract data from a recordset for processing?

  • Thread starter Thread starter Malcolm Potter
  • Start date Start date
M

Malcolm Potter

Folks



I have generated a procedure incorporating a DAO recordset which is based
upon a table and indexed to a field called 'Player'. The maximum number of
records for any player appearing in this recordset will be nine. What I now
need to do is to select out each player's set of results in turn so that
they may be separately processed. The end result of this processing should
give a player's name and score which then needs to be stored in a separate
table along with the results for all other players.



I would be grateful for any general advice on the way ahead.



Regards



Malcolm
 
Here's a procedure that would work. You'd run it once for
each player. I can't debug it on the machine I'm on right
now, but it should be pretty close.

eg) ExtractInfo rsMyScores, "John"


Public Sub ExtractInfo(rsRead as DAO.Recordset, _
Player as string)
Dim strSQL
Dim rsWrite as DAO.Recordset
Dim intScore as Integer
Set rsWrite = CurrentDb.OpenRecordset _
("myTable",dbOpenDynaset, dbAppendOnly)
strSQL = "[Player]='" & Plyr & "'"
With rsRead
.FindFirst strSQL
If .NoMatch Then
Exit Sub
Else
Do Until .NoMatch
intScore = intScore + !Score
.FindNext strSQL
Loop
rsWrite.AddNew
rsWrite!Player = Plyr
rsWrite!TotalScore = intScore
rsWrite.Update
End If
End With
End Sub
 
Folks



I have generated a procedure incorporating a DAO recordset which is based
upon a table and indexed to a field called 'Player'. The maximum number of
records for any player appearing in this recordset will be nine. What I now
need to do is to select out each player's set of results in turn so that
they may be separately processed. The end result of this processing should
give a player's name and score which then needs to be stored in a separate
table along with the results for all other players.
Click to expand...

Is there some particular reason to do this in VBA code? It might be
easier to create a Totals query, using the same criteria as the
recordset; you could then base an Append query upon the totals query
to populate the final table.

If you do want to use the recordset in VBA, you'll want to be sure
that the recordset is sorted by player; you can then use the MoveFirst
method of the recordset to move to the first record, and a loop using
MoveNext to step through it.
 
Elwin



Thanks for your advice. I think some of the code will help me over the next
hurdle however by trying to keep the description of my problem as simple as
possible I think I might have misled you. Assuming that a player has nine
results in the recordset, I thought I needed to copy the nine records
containing these results into another table for processing. The following
notes might help to shed further light the on the matter:



1. The fields contained within my recordset are 'ResultID', 'Date', 'Player'
, PlayersBand', 'Partner', 'PartnersBand' and 'Points'.

2. A 'Player' will have one of the following three bands - 'A', 'B' or 'V'
(for visitor).

3. In the processing table only the best six out of nine results will count.

4. Of these six results an 'A' band player must have a minimum of four
results with 'B' band partners.

5. The six results for 'B' band players may be achieved with any mix of 'A'
and 'B' band players.

6. Any result which either 'A' or 'B' band players achieve when partnering a
'V' band player, will attract a zero score.

7. If an 'A' or 'B' band player plays with the same partner more than once,
only the top score will count.



Having previously managed to do the above in Excel, via a mixture of 'on
sheet' calculations and VB code, I thought I'd try to achieve the same in
Access. Needless to say I'm not finding it easy.



Regards



Malcolm



Elwin said:
Here's a procedure that would work. You'd run it once for
each player. I can't debug it on the machine I'm on right
now, but it should be pretty close.

eg) ExtractInfo rsMyScores, "John"


Public Sub ExtractInfo(rsRead as DAO.Recordset, _
Player as string)
Dim strSQL
Dim rsWrite as DAO.Recordset
Dim intScore as Integer
Set rsWrite = CurrentDb.OpenRecordset _
("myTable",dbOpenDynaset, dbAppendOnly)
strSQL = "[Player]='" & Plyr & "'"
With rsRead
.FindFirst strSQL
If .NoMatch Then
Exit Sub
Else
Do Until .NoMatch
intScore = intScore + !Score
.FindNext strSQL
Loop
rsWrite.AddNew
rsWrite!Player = Plyr
rsWrite!TotalScore = intScore
rsWrite.Update
End If
End With
End Sub
-----Original Message-----
Folks



I have generated a procedure incorporating a DAO recordset which is based
upon a table and indexed to a field called 'Player'. The maximum number of
records for any player appearing in this recordset will be nine. What I now
need to do is to select out each player's set of results in turn so that
they may be separately processed. The end result of this processing should
give a player's name and score which then needs to be stored in a separate
table along with the results for all other players.



I would be grateful for any general advice on the way ahead.



Regards



Malcolm


.
Click to expand...
Click to expand...
 
John



Thanks for your advice. I think that by trying to keep the description of my
problem as simple as possible I might have misled you. Assuming that a
player has nine results in the recordset, I thought I needed to copy the
nine records containing these results into another table for processing. The
following notes might help to shed further light the on the matter:



1. The fields contained within my recordset are 'ResultID', 'Date', 'Player'
, PlayersBand', 'Partner', 'PartnersBand' and 'Points'.

2. A 'Player' will have one of the following three bands - 'A', 'B' or 'V'
(for visitor).

3. In the processing table only the best six out of nine results will count.

4. Of these six results an 'A' band player must have a minimum of four
results with 'B' band partners.

5. The six results for 'B' band players may be achieved with any mix of 'A'
and 'B' band players.

6. Any result which either 'A' or 'B' band players achieve when partnering a
'V' band player, will attract a zero score.

7. If an 'A' or 'B' band player plays with the same partner more than once,
only the top score will count.



Having previously managed to do the above in Excel, via a mixture of 'on
sheet' calculations and VB code, I thought I'd try to achieve the same in
Access. Needless to say I'm not finding it easy. I have already written a
procedure which sets up a recordset indexed on the player's name and then
loops through it displaying the required data in the immediate window. The
immediate task which I am faced with is to copy the results for each player
into a separate table in turn, so that I may process them according to the
above requirements.



Regards



Malcolm
 
Back
Top