# Thread: Multi-calculation query - is there a way to do this?

1. Registered User
Join Date
Apr 2007
Posts
108

## Unanswered: Multi-calculation query - is there a way to do this?

I have a table with every NBA game from 2007 season.

Table has following fields:

GameID
GameDate
HomeTeam
AwayTeam
HomeTeamScore
AwayTeamScore
HomeTeamRatingPriorToTheCurrentGame
AwayTeamRatingPriorToTheCurrentGame

Now, we come to the problem, I want to calculate HomeTeam and AwayTeam's ratings for each game based on their performance up to the current GameDate!

Every team starts a season with 1000 points and gets points added and deducted based on their results and based on to whom they win or lose.

For example if a team with 1500 rating beats 500 rated team it gets less points that when a 1500 rated team beats 1200 rated team.

So, I would need to design the query that can do this (explained in example in plain language):

On 15th November 2006 Seattle (at home, playing 9th game in the season) plays Philadelphia (visitor, playing 7th game in the season).

I want to determine Seattle's and Philadelphia's ratings prior to that game.

I need to pull last Seattle's game (be it away or at home, in this case on 13th of November vs. NJ), retrieve its rating and add or subtract points based on their performance vs. New Jersey.

Then repeat the same for away club Philadelphia!

And then repeat that for every game in a season.

I have no idea how to nest that query, especially given the fact that each new row should take into account calculated ratings of rows above it!

Anybody can help?

2. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
wow. just wow.

I very much doubt you can do this with a set based solution.
You will probably have to use a recordset and iterate through the calculations.

I think the rules here are too complex at the moment. Perhaps you should break it down into very minor steps.

Ugh, I know, another cop out (count 2 tonight) but that's one of the most evil questions I've heard!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
well I'd guess they key to that is not to store the rating for a series of games in a table.

if you identify the date the game was played (and you have) you should design an algolryhtm which calculates the rating based on a date interval.

If you have to store the rating, for performance or other reasons then logically I'd expect to store the rating for this game (and preceeding games), not rating for previous games in this game.

once you have decided on your algolrhtym convert that into a query
eg
select (<mycomplexratingmathmaticalfunction>) as Rating from <mytable> where gameplayedon between (#startdate# and #enddate#) where teamid=<blah>

you then need to associate that with the parent SQL I forget what the sub query syntax is.. truth be told, I only recently found that Access supported sub queries thanks to this forum and I've never really used them enough to sink in.

I still don't think its right to store this as its derived data. However I think you could argue that you needed to know the teams ratings going into a game for each game they've played, if you need that available for each and every game (ie seattle were n% after game1, o% after game2, z% after game13. It sounds all "too American" sports to me.

4. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by georgev
I very much doubt you can do this with a set based solution.
You will probably have to use a recordset and iterate through the calculations.
wow. just wow.

please go stand in the corner for an hour

5. www.gvee.co.uk
Join Date
Jan 2007
Location
UK
Posts
11,445
Sorry Sir.

*stands in the corner awaiting your solution*

6. Registered User
Join Date
Apr 2007
Posts
108
wow. just wow.

please go stand in the corner for an hour
If it's possible r937 can you offer a solution to this problem?

OR

To rephrase the question:

Can you reference a calculated field in another row (other then the current row) within the same query?

For example, imagine simple table with 3 fields:

IDPrimaryKey
NumberOne
NumberTwo

You then design a query like this

SELECT IDPrimaryKey, NumberOne, NumberTwo, SumOfTwoNumbers AS [NumberOne+NumberTwo] FROM bla.................

And now you want to add another field to that query for example SumOfTwoNumbers(calculated row)_From_Row_That_Has_IDPrimaryKey_OF_CurrentRow' s_IDPrimaryKey_Minus_5.

That would solve the above mentioned problem!

7. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Originally Posted by Riorin
If it's possible r937 can you offer a solution to this problem?
sorry, not at this time

8. Registered User
Join Date
Apr 2007
Posts
108
sorry, not at this time
1. No time, will do it later?
2. I don't know the solution!
3. I know, but won't share now or ever!

9. SQL Consultant
Join Date
Apr 2002
Location