CODEWITHSUNDEEP
sqlms-accessms-access-2003
Coding Enthusiast
Coding Enthusiast

Reputation: 3933

updating a table using the max date and values from another table using sql in ms-access

I have with the following scenario: I want to update tableB using the rating from tableA. The challenge is: the ratings change randomly and when updating tableB's records, I have the take in account the date of the match and if the rating changed let's say on Tuesday and the game was on the monday before, I want the rating to be the previous rating not the latest one. 

#table A: contains rating of players, changes randomly at any date depending
#on drop of form from the players  

PID| Rating | DateChange  |
1  |    2   | 10-May-2014 |
1  |    4   | 20-May-2015 |
1  |   20   | 1-June-2015 |
2  |    4   | 1-April-2014|
3  |    4   | 5-April-2014|
2  |    3   | 3-May-2015  |

#Table B: contains match sheets. Every player has a different match sheet
#and plays different dates.

MsID | PID  | MatchDate    | Win | Rating |
 1   |  2   | 10-May-2014  |  No |    0   |
 2   |  1   | 15-May-2015  | Yes |    0   |
 3   |  3   | 10-Apr-2014  |  No |    0   |
 4   |  1   | 21-Apr-2015  | Yes |    0   |
 5   |  1   | 3-June-2015  | Yes |    0   |
 6   |  2   | 5-May-2015   |  No |    0   |

#I am trying to achieve this by running the ms-access query: i want to get
#every players rating at the time the match was played not his current
#rating. 

MsID | PID  | MatchDate    | Rating |
 1   |  2   | 10-May-2014  |    4   |
 2   |  1   | 15-May-2015  |    2   |
 3   |  3   | 10-Apr-2014  |    4   |
 4   |  1   | 21-Apr-2015  |    2   |
 5   |  1   | 3-June-2015  |    20  |
 6   |  2   | 5-May-2015   |    3   |

I tried the following code: 

Update [B-table] as wdev
set wdev.rating = ( SELECT B.MsID, B.PID, B.MatchDate, A.rating as Rating 
FROM [B-table] B
INNER JOIN [A-table] A
  on B.PID = A.PID
INNER JOIN (     
  SELECT MAX(Y.DateChange) MDC, Y.PID, Z.Matchdate
  FROM [B-table] Z
  INNER Join [A-table] Y
   on Z.PID = Y.PID
  and Y.DateChange <= Z.MatchDate
  GROUP BY Y.PID, Z.Matchdate) C
  on C.mdc = A.DateChange
 and A.PID = C.PId
 and B.MatchDate = C.Matchdate) And B.MsID = Wdev.MsID

SQLFiddle with database schema

In summary: I want the rating corresponding to the max date change on or before date of match.

Upvotes: 0

Views: 1473

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

I think you just want a correlated subquery:

update [B-table] as b
    set rating = (select top 1 rating
                  from [A-table] as a
                  where a.pid = b.pid and
                        a.datechange <= b.matchdate
                  order by a.datechange desc
                 ) ;

Note: because of the way that MS Access processes top, the subquery could return multiple rows in the event of a tie. The normal solution to this is to include an additional key value in the order by, to prevent ties. However, there does not seem to be a unique key in the "a" table.

Upvotes: 1

Related Questions