Making a calculated feild.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

This is my first attempt at using a database!

I have a 'Players' table holding 'PlayerName' and 'CurrentRating'

joined to a 'Pairings' table holding 'Player' and 'Opponent' (who are in
PlayerName)

I need to make a calculated field 'PlayerCurrentRating' -
'OpponentCurrentRating'

Please Help!
 
You can add the Players table twice to a query with the Pairings table. Join
PlayerName from one copy of the Players table to Player and join PlayerName
from the other copy of Players to the Opponent field. This should allow you
to view both player's CurrentRating.
 
Thanks Duane,
I had already made the double join, but am having trouble phrasing the
calculated field to get Difference: PlayerCurrentRating - (minus)
OpponentCurrentRating
Any suggestions appreciated.
 
Yorkie118 said:
This is my first attempt at using a database!

I have a 'Players' table holding 'PlayerName' and 'CurrentRating'

joined to a 'Pairings' table holding 'Player' and 'Opponent' (who are in
PlayerName)

I need to make a calculated field 'PlayerCurrentRating' -
'OpponentCurrentRating'
Click to expand...

Why have two tables? I'd make one with the following fields:

Player ID, Player Name, Current Rating, Opponent ID

Then the Opponent ID would point back to the Players table since Opponents
are Players, too.
Add the table to a new query twice, linking Player ID field. Here's the
SQL:

SELECT [Player Table].PlayerID, [Player Table].PlayerName, [Player
Table].CurrentRating, [Player Table].OpponentID, [Player Table_1].PlayerName
AS [Opponent Name], [Player Table].[CurrentRating]-[Player
Table_1].[CurrentRating] AS [Rating Difference]
FROM [Player Table] LEFT JOIN [Player Table] AS [Player Table_1] ON [Player
Table].PlayerID = [Player Table_1].OpponentID;


Tom Lake
 
Thanks Duane, apologies if this is a duplicate, I sent one quite a while ago.
I had already made the double join, but am having trouble phrasing the
calculated field Difference:PlayerCurrentRating -(minus)
OpponentCurrentRating.
Any help appreciated, thanks again.
 
Try this:

I am assuming you have one table.

Create a new Query with your fields from your table you want to
calculate.
On a blank field in your Query type:

FieldName: NZ([FieldName1]) - NZ([FieldName2])

Then click on the Sums Button (looks like a Greek E) at the top and you
will see a new row titled Total: appear and it will have Group By in
the cells. You want to click on the drop down menu for the field you
put your formula in and click Expression.

Try to run your query. Make sure you type something in your table in
order to test it.
 
Wow! Thanks Tom, as I said, this is my first foray into building a database,
so the SQL is still foreign to me! I did get it typed in , but it runs as a
Parameter query.
Im wanting the query to look up the values. any thoughts?

Tom Lake said:
Yorkie118 said:
This is my first attempt at using a database!

I have a 'Players' table holding 'PlayerName' and 'CurrentRating'

joined to a 'Pairings' table holding 'Player' and 'Opponent' (who are in
PlayerName)

I need to make a calculated field 'PlayerCurrentRating' -
'OpponentCurrentRating'
Click to expand...

Why have two tables? I'd make one with the following fields:

Player ID, Player Name, Current Rating, Opponent ID

Then the Opponent ID would point back to the Players table since Opponents
are Players, too.
Add the table to a new query twice, linking Player ID field. Here's the
SQL:

SELECT [Player Table].PlayerID, [Player Table].PlayerName, [Player
Table].CurrentRating, [Player Table].OpponentID, [Player Table_1].PlayerName
AS [Opponent Name], [Player Table].[CurrentRating]-[Player
Table_1].[CurrentRating] AS [Rating Difference]
FROM [Player Table] LEFT JOIN [Player Table] AS [Player Table_1] ON [Player
Table].PlayerID = [Player Table_1].OpponentID;


Tom Lake
Click to expand...
 
Try:
Difference: Players.CurrentRating -Players_1.CurrentRating

If this doesn't work, please post back with your full sql view of the query.
 
Huh?
If anything, I would add a PlayerID field to the Players table and change
the Pairings table to PlayerID and OpponentID. Each of these fields would
store a PlayerID value from the Players table.

My preference is to store a name value only once in an application. Use an
ID everywhere else so the name value can be looked up.

--
Duane Hookom
MS Access MVP
--


Tom Lake said:
Yorkie118 said:
This is my first attempt at using a database!

I have a 'Players' table holding 'PlayerName' and 'CurrentRating'

joined to a 'Pairings' table holding 'Player' and 'Opponent' (who are in
PlayerName)

I need to make a calculated field 'PlayerCurrentRating' -
'OpponentCurrentRating'
Click to expand...

Why have two tables? I'd make one with the following fields:

Player ID, Player Name, Current Rating, Opponent ID

Then the Opponent ID would point back to the Players table since Opponents
are Players, too.
Add the table to a new query twice, linking Player ID field. Here's the
SQL:

SELECT [Player Table].PlayerID, [Player Table].PlayerName, [Player
Table].CurrentRating, [Player Table].OpponentID, [Player
Table_1].PlayerName AS [Opponent Name], [Player
Table].[CurrentRating]-[Player Table_1].[CurrentRating] AS [Rating
Difference]
FROM [Player Table] LEFT JOIN [Player Table] AS [Player Table_1] ON
[Player Table].PlayerID = [Player Table_1].OpponentID;


Tom Lake
Click to expand...
 
Thanks to all of you - it's great to know that help is at hand.
Duane won the cigar, as I preferred to keep the player table as it was, (I
don't know any better yet!). I really think I can make progress from now on.

Duane Hookom said:
Huh?
If anything, I would add a PlayerID field to the Players table and change
the Pairings table to PlayerID and OpponentID. Each of these fields would
store a PlayerID value from the Players table.

My preference is to store a name value only once in an application. Use an
ID everywhere else so the name value can be looked up.

--
Duane Hookom
MS Access MVP
--


Tom Lake said:
Yorkie118 said:
This is my first attempt at using a database!

I have a 'Players' table holding 'PlayerName' and 'CurrentRating'

joined to a 'Pairings' table holding 'Player' and 'Opponent' (who are in
PlayerName)

I need to make a calculated field 'PlayerCurrentRating' -
'OpponentCurrentRating'
Click to expand...

Why have two tables? I'd make one with the following fields:

Player ID, Player Name, Current Rating, Opponent ID

Then the Opponent ID would point back to the Players table since Opponents
are Players, too.
Add the table to a new query twice, linking Player ID field. Here's the
SQL:

SELECT [Player Table].PlayerID, [Player Table].PlayerName, [Player
Table].CurrentRating, [Player Table].OpponentID, [Player
Table_1].PlayerName AS [Opponent Name], [Player
Table].[CurrentRating]-[Player Table_1].[CurrentRating] AS [Rating
Difference]
FROM [Player Table] LEFT JOIN [Player Table] AS [Player Table_1] ON
[Player Table].PlayerID = [Player Table_1].OpponentID;


Tom Lake
Click to expand...
Click to expand...
 
Huh?
If anything, I would add a PlayerID field to the Players table and change
the Pairings table to PlayerID and OpponentID. Each of these fields would
store a PlayerID value from the Players table.

My preference is to store a name value only once in an application. Use an
ID everywhere else so the name value can be looked up.
Click to expand...

That's exactly what my scheme does. The Players Table is the only one to
have the names in it.
Any other table can store the just PlayerID and look it up in the Players
Table if the name is needed.

Tom Lake
 
However, having these fields in a table suggests that a player will only
ever have one opponent.
Player ID, Player Name, Current Rating, Opponent ID

I am fairly certain there will be more than one pairing record per player or
pair of players.
 
Back
Top