Elo Access Function

[a1ehouse]

I'm after some VBA code in Access to allow me to calculate Elo ratings from historical data. Searched and read a heck of a lot of posts and Googled for hours but can not find an answer. I have got functions etc to do the math but my problem is that I only have 1000's of rows of historical data and a basic idea of how I want an Access query to run. Before spending any more time on trying to fathom it out I was wondering if anyone has the function all ready!?!? Essentially the function should take the old rating, then append and update the home and away ratings depending on the result. Simple huh?!?!? Any advice greatful and can post functions I already have if it helps.

[Fat Jack]

Re: Elo Access Function

I'm after some VBA code in Access to allow me to calculate Elo ratings from historical data. Searched and read a heck of a lot of posts and Googled for hours but can not find an answer. I have got functions etc to do the math but my problem is that I only have 1000's of rows of historical data and a basic idea of how I want an Access query to run. Before spending any more time on trying to fathom it out I was wondering if anyone has the function all ready!?!? Essentially the function should take the old rating, then append and update the home and away ratings depending on the result. Simple huh?!?!? Any advice greatful and can post functions I already have if it helps.

a1ehouse How you do this will depend on how the data is structured. From what you've said it looks like you need some help with retrieving and updating the data? First off, are the match results all in one table? I presume so - I'll assume so!! In your VBA you'll need to first get all the matches you need and loop through them. Before going further you need to know how to do this (google access vba recordset and looping). I don't want to sound to pedantic so sorry if I do! Haven't looked at Access VBA/SQL for years but your pseudo code will be something like this: select all matches (order by match date asc) loop

get home/away, etc select prev ELO rating for team

**** this is where it will be a bit complicated **** What you will have to do is use a second query, this time selecting prev matches for this team (you can build the query for this separately and then call it from your VBA). This is complicated because you need to find the last home match and last away match, as you are dealing with the same data (team name) in two separate fields (home team and away team). What you need is the last played macth for this team to retrieve the ELO rating. You will need this for both teams Do ELO calc for both teams Update match record

end loop Is this along the right lines? FJ