Convert SQL Server procedure to Access VBA

[maxlang]

Office 2003/Win7
I would like to convert some SQL Server procedures to Access VBA.

The procedures are used to calculate chess player ratings.
Basically player's ratings are updated monthly based on results against
rated opponents based on the work of stats Prof. Mark Glickman.
I have done some of the conversion and it runs OK on test tables and queries but am stuck on the first UPDATE procedure.

These procedures convert fine:
TRUNCATE TABLE #tmpRatedResults

DECLARE @GameMonth int
SELECT @GameMonth = 1
DECLARE @CValue decimal (12,2)
SELECT @CValue = 63.2

WHILE @GameMonth <=60 BEGIN
INSERT #tmpRatings (RatingMonthID, PID, Rating, RD, MonthEnding)
SELECT @GameMonth as RatingMonthID, PID, Rating, RD, MonthEnding
FROM #tmpGlickoRatings

TRUNCATE TABLE #tmpCurrentActive
INSERT #tmpCurrentActive (PID)
SELECT PID
FROM training_union
WHERE MonthID = @GameMonth
GROUP BY PID

However when I get to the next procedure I am unsure as to how to code this:

UPDATE CA
SET CA.RD = CASE
WHEN SQRT(GR.RD*GR.RD + @CValue*(@GameMonth - MonthEnding)) <350 THEN SQRT(GR.RD*GR.RD + @CValue*(@GameMonth - MonthEnding)) ELSE 350 END,
CA.r = GR.Rating
FROM #tmpCurrentActive CA
JOIN #tmpGlickoRatings GR ON CA.PID = GR.PID

Any suggestions or advice much appreciated!

 

[xenou]

Does this query work (on its own):

SELECT PID, 1.00/(SUM(q*q) as d2 FROM tmpCR3 GROUP BY PID

PS: did you consider using SQL Server Express for this project? It seems a bit of a pain to convert it all to Access when its already working in SQL Server. I think you could front-end Access on to the SQL server express backend and get the best of both worlds ... though I don't have personal experience with this setup.

 

[maxlang]

Hi xenou!
Yes the procedure works when split into two separate queries.

As to why not use SERVER, very astute! and that is what I have decided to do. Originally I thought the Server procedure was so vanilla it would be a piece of cake to do the conversion plus I am the only db user so a platform designed for multi-user environment seemed overkill. However it has dawned on me that starter version, SQL Server 2008 Express, may be the way to go after all.
Many thanks for your valuable assistance!