Finding a Value based on two Criteria.

[Tommy888]

Hi,

I have a query that contains two fields that are used to calculate points from another table and add a new field in the query called GamePoints.

Here are the relevent entities:

qry_GameResults

PlayerID
Player Rank
GameID
GameTypeID
GamePoints

tbl_PosistionandPoints

GameTypeID
Posistion
Points

The idea is that the qry_GameResults, uses the GameTypeID and Player Rank fields as a crtieria for finding the Points. So if the Posistion in the table is the same as Player Rank and the GameTypeID in the table is the same as the GameTypeID in the query then the value of the points from the table can be found and used as a field in the query called GamePoints.

This is my rough attempt at writing it in SQL, but I dont think its right.

SELECT Points FROM tbl_PosistionandPoints
WHERE Posistion = Rank
AND GametypeID = GametypeID;

Any help would be appreciated.
Tom

 

[WayneRyan]

Tom,

I think this is it:

Select qry_GameResults.PlayerID,
       qry_GameResults.[Player Rank],
       qry_GameResults.[GameID],
       qry_GameResults.[GameTypeID],
       Sum(tbl_PositionandPoints.Points)
From   qry_GameResults Inner Join tbl_PositionandPoints On
         qry_GameResults.[Player Rank] = tbl_PositionandPoints.Position And
         qry_GameResults.GameTypeID = tbl_PositionandPoints.GameTypeID

Wayne

 

[Tommy888]

Thanks, this looks like it will work, I keep getting a syntax error when I come to run it though. Ive tried but cant find the reason why it wont work?

(missing operator) in query expression".

It then highlights
qry_GameResults.[Player Rank] = tbl_PositionandPoints.Position

Select qry_GameResults.PlayerID,
       qry_GameResults.[Player Rank],
       qry_GameResults.[GameID],
       qry_GameResults.[GameTypeID],
       Sum(tbl_PositionandPoints.Points)
From   qry_GameResults Inner Join tbl_PositionandPoints On
           qry_GameResults.[Player Rank] = tbl_PositionandPoints.Position And
           qry_GameResults.GameTypeID = tbl_PositionandPoints.GameTypeID;