Hi all. I'm trying (the operative word!) to build a query for an Access tennis database that is provided and updated by a third party. The query will be based on a table of historic match results that has the Date, the Winning Player and the Losing Player. The query needs to assign a fixed numeric Current Score (let's say 100) to a player if it is their first match in the database. Based upon whether they then win or lose the query needs to calculate a New Score for them. The New Score calculation is complicated but for simplicity's sake let's say the Winning Player gets +5 and the Losing Player -5. Now the next time one of the players plays I need the query to 'lookup' what their New Score was for their previous match and this becomes the Current Score for that match.
So if Federer beats Nadal and this match is both players' first match in the database then they the query will give them both a Current Score of 100. It will also give Federer a New Score of 105 and Nadal a New Score of 95. The next time Federer plays then his Current Score will be 105 and the next time Nadal plays then his Current Score will be 95. It's important to highlight at this stage that players can obviously appear in either the winning or Losing Player column.
Currently I export the table to Excel, order it in decending order of date and then use if statements and lookups to find the last record for a player in either the Winning Player or Losing Player columns. However, Excel is starting to really strain with the weight of 20,000 matches and I want to increase this to circa 300,000!
It's safe to say that I have no idea how to replicate the Excel functionality in Access and googling 'Access lookups' brings up a never ending list of simple 'how to lookup using queries' pages. I'm stuck - any ideas??
So if Federer beats Nadal and this match is both players' first match in the database then they the query will give them both a Current Score of 100. It will also give Federer a New Score of 105 and Nadal a New Score of 95. The next time Federer plays then his Current Score will be 105 and the next time Nadal plays then his Current Score will be 95. It's important to highlight at this stage that players can obviously appear in either the winning or Losing Player column.
Currently I export the table to Excel, order it in decending order of date and then use if statements and lookups to find the last record for a player in either the Winning Player or Losing Player columns. However, Excel is starting to really strain with the weight of 20,000 matches and I want to increase this to circa 300,000!
It's safe to say that I have no idea how to replicate the Excel functionality in Access and googling 'Access lookups' brings up a never ending list of simple 'how to lookup using queries' pages. I'm stuck - any ideas??