How to update these figures?

[Guest]

I have a NEWRATINGS Query which calculates WINNERNEWRATING and LOSERNEWRATING
in Calculated Fields.
I now want to update RATING (in PLAYER Table) to WinnerNewRating, and
RATING (in PLAYER_1 Table) to LoserNewRating.

Help Please!

 

[Guest]

It sounds like you've got multiple tables that could probably better track
info without duplication if the "winner rating" and "loser rating" were both
kept in the same table under two separate columns, instead of two separate
tables with an identically named column, Rating.

However, one could try the following two update queries:

UPDATE Player INNER JOIN NEWRATINGS ON Player.PID = NEWRATINGS.PID
SET Rating = WinnerNewRating;

.. . . and . . .

UPDATE Player_1 INNER JOIN NEWRATINGS ON Player_1.PID = NEWRATINGS.PID
SET Rating = LoserNewRating;

.. . . where Player and Player_1 are the names of the tables to be updated,
NEWRATINGS is the name of the query where the WinnerNewRating and
LoserNewRating columns are calculated, and PID is the primary key for the
Player and Player_1 tables.

And if you kept the winning and losing data all in one table, the single
update query would be:

UPDATE Player INNER JOIN NEWRATINGS ON Player.PID = NEWRATINGS.PID
SET WinnerRating = WinnerNewRating, LoserRating = LoserNewRating;

.. . . where WinnerRating is the player's rating as a winner and LoserRating
is the same player's rating as a loser.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Hi Gunny, Thanks for reply, and apologies for delay (overnight in UK)
This is my first effort, and I'm still confused.
What I have is:-
a Player table including PlayerID, Rating.
a Results table inc. WinnerID, LoserID (both in PlayerID), Score
a NewRatings Query inc WinnerID, Player.Rating, LoserID, Player_1.Rating,
Score, Points, (calculated), WinnerNewRating (Player.Rating+Points),
LoserNewRating (Player_1.Rating-Points)
I need to update the Player table to the winner and loser's new ratings.
Hope this clarifies.

 

[Guest]

If I understand your structure correctly, then to keep track of two separate
ratings for each player, you need to alter your Player table. Change the
Rating column name to WinnerRating and add a new column, LoserRating. Then
use the following two update queries:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.WinnerID
SET WinnerRating = WinnerNewRating;

.. . . and:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.LoserID
SET LoserRating = LoserNewRating;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Hi Gunny, I am only keeping one rating for each player (in Players table).
This Rating goes up after a win (as a winner) and down after a loss (as a
loser). Everything works ok until I update the winners rating. Access then
re-rates the loser, based on the winners new rating. I need to simultaneously
update both ratings. P.S I've had my wrist slapped by Fred, as I asked again
for help, not realising this was bad ettiquette, so I will understand if you
dont reply. Thanks, Yorkie

 

[Guest]

You can update a single column, Rating, in the Player table with two
sequential update queries (one for winners, then one for losers), with the
caveat that the WinnerID and LoserID are different players in the NewRatings
query. In other words, the NewRatings query calculates winners' and losers's
ratings, but not for winners who are also losers, nor for losers who are also
winners. WinnerID and LoserID in the NewRatings query must refer to the
primary key of the Players table, PlayerID, so that a join can be made to
target the appropriate records.

Try:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.WinnerID
SET Rating = WinnerNewRating;

.. . . and then run:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.LoserID
SET Rating = LoserNewRating;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Getting close I hope Gunny.
However, running the query now produces the error message "The Specified
field Rating could refer to more than one table listed in the FROM clause of
your SQL statement"
Yorkie

 

[Guest]

Sorry about that. Your NewRatings query also contains the field of the same
name, so the table name must also be identified in this update query. Try:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.WinnerID
SET Player.Rating = WinnerNewRating;

.. . . and then run:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.LoserID
SET Player.Rating = LoserNewRating;

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Gunny, sorry to say that we've hit the same problem that I had originally! By
running e.g the winners update, Players table updates fine for the winner,
but then updates this figure before we run the second update query. So in
effect whats happening is say e.g two players each rated 1000 play, A beats
B, Points involved are +/- 15. We run update winner query, which makes A
1015.Fine!. Now Access is passing this 1015 to the update query, so when we
update loser, he loses only 14 pts from his rating.
I wonder, could we move WinnerNewRating to a temporary table whilst we
update LoserNewRating to Player table, and then update Winner from here?

 

[Guest]

Your NewRatings query doesn't meet the criteria I outlined in my previous
post. Therefore, you'll need to alter the query (or create a new one) so
that it calculates what the player's actual new rating will be, not a
separate winner's rating and loser's rating.

Use this new rating as the column to update the Rating column in the Player
table. Try the following two update queries:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.WinnerID
SET Player.Rating = NewRating;

.. . . and then run:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.LoserID
SET Player.Rating = NewRating;

Or, if you get rid of the WinnerID and LoserID in the NewRatings query, then
try:

UPDATE Player INNER JOIN NewRatings ON Player.PlayerID = NewRatings.PlayerID
SET Player.Rating = NewRating;

.. . . where the NewRatings query lists the PlayerID, not a separate WinnerID
and LoserID.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.

 

[Guest]

Bingo! Gunny, you don't know how grateful I am! I can now get on with my life!
Let me please thank you for all your time,effort and patience. Its good to
know that help is out there, and if I ever learn a quarter of your knowledge,
I shall be well pleased. Thanks again, Yorkie

 

['69 Camaro]

You're welcome. I'm glad you got it working. And thank you for the
compliment. The knowledge is gained by using the product -- and by helping
others with their database applications. Helping others will actually
accelerate the learning process because one will encounter a much broader
range of problems and situations than would generally be encountered as a
single developer or within a single company.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.