VBA Help Required For Horse Racing - Please

[The Brown Growler]

Hi,

Would anyone please be able to assist me with some VBA code to be used in an access database ?

I have an access table containing the horse name, the race track, the date and the official rating that the horse performed to on that date. I hope to be able to compare the rating achieved on any date with the rating achieved on the previous data when the race tracks are the same. If the rating increased by comparison to the previous value then a calculated field should be "+R" if the rating decreased then the value should be"-R" and if no change, "R". If there are no occasions of the horse running at the track previously then the value should be "R"

I have pasted below an example that illustrates the principle:

Horse Racecourse Date Rating Rating Change
Iruntoomuch Wolverhampton 26/09/2009 67 -R
Iruntoomuch Wolverhampton 25/09/2009 68 +R
Iruntoomuch Wolverhampton 24/09/2009 65 +R
Iruntoomuch Lingfield....... 23/09/2009 66 R
Iruntoomuch Lingfield....... 22/09/2009 66 +R
Iruntoomuch Lingfield....... 21/09/2009 65 R
Iruntoomuch Wolverhampton 20/09/2009 64 +R
Iruntoomuch Wolverhampton 19/09/2009 63 +R
Iruntoomuch Wolverhampton 18/09/2009 62 R

I hope to be able to run the code by clicking a button on a form that either directly runs the code from the button "onclick" property or runs a module containing the code.


Any help most appreciated.

Thx & Rgds
Growlos

 

[LPurvis]

Hi

You can determine the previous rating by querying alone.
For example

SELECT T.Horse, 
    T.Racecourse, 
    T.Date, T.Rating, 
    (SELECT TOP 1 R.Rating 
     FROM tblRaces R 
     WHERE T.Horse = R.Horse AND T.Racecourse = R.Racecourse AND T.Date > R.Date 
     ORDER BY R.Date DESC
    ) AS PrevRating
FROM tblRaces AS T

Then it's a direct comparison between the current and previous to determine the display value (R, -R, +R).
I'd actually advocate then using a VBA function to determine that display value, rather than doing so in the query. To either perform that subquery again for or refer to its alaised value will just add unecessary overhead on the database. (True the VBA call will add some overhead - but that should be comparatively less and also is all on the client, rather than hitting the database and network again).

e.g. VBA function:

Function fDisplayVal(varVal, varPrev)
 
    fDisplayVal = IIF(varVal > varPrev, "+R", IIF(varPrev > varVal, "-R", "R"))
    
End Function

Making the query then:

SELECT T.Horse, 
    T.Racecourse, 
    T.Date, T.Rating, 
    fDisplayVal(T.Rating, (SELECT TOP 1 R.Rating 
     FROM tblRaces R 
     WHERE T.Horse = R.Horse AND T.Racecourse = R.Racecourse AND T.Date > R.Date 
     ORDER BY R.Date DESC
    )) AS RatingCompare
FROM tblRaces AS T

Cheers.

 

[LPurvis]

Of course, there is an assumption made that a TOP 1 predicate will always be able to retrieve precisely one row. Your table containing a primary key would be enough to ensure that. (So it's not a very big assumption IMHO ;-)

 

[gemma-the-husky]

why +R, -R

surely you should be storing the actual rating change

ie +2 -2, 0

far more informative.

----------
note that in normal circumstances, referring to a previous event is dangerous. if you store a form improvement of +5, say, and then interleave a race, then you will need to change the improvement previously recorded against the next race

ie race 30/6/09 - form 75
race 17/9/09 - form 72 -3

but then you interleave

race 16/8/09 - form 80 so now

race 16/8/09 is 80 +5
and
race 17/9/09 - form 72 -8 (not -3 as before)

but if you enter races in sequence this shouldnt matter.

------------
its really a matter of taste whether you store the performance change, or evaluate it when you need it. if you use it a lot, probably store it. However - see below

However, since you may want the flexibility to compare ratings achieved as you say on tracks, but also on a number of other stats you dont mention - eg different race distances/ time between runs/ date of race/ first time out/ going/ no of runners/ slow-fast race time/ wonby/ race category/ odds/ betting movements/ was it favourite / second favourite / different jockeys, or even whether the jockey was a claiming apprentice/ then it may be better NOT to store the change, just work it out as you need it.

Not sure if you have given us a full list of everything you are monitoring - personally i think the going is one of the more important variables - depends how much faith you have in the official handicapper i suppose.

Note that it is possible that you can get a lot of this stuff as a download in some shape or other.



gemma the greyhound for this one!

 

[The Brown Growler]

LPurvis,

Thx, I am using your query code to get the current and previous rating, now you have provided the code for that I should be OK with the comparison. One point, the 1st query is taking quite a while to run, I had approx 280,000 records and the query is still running. Does that sound OK ? My concern is that I made a mistake with the code and I have set off some enormous loop ?

Dave,

I do not wish to use a mathematical difference in ratings, I only wish to know if the Horse's rating was up or down or the same compared to its previous rating per race course. I then hope to use the up, down or same values as the basis of a data mining analysis of a large data set. You are also correct in your assumption that there are a lot of other variables in the mix.


Thx & Rgds for the help
Growlos

 

[LPurvis]

Total number of records is always going to be an issue with this.
Corrolated subqueries take far greater processing.
You're inevitably going to feel 200,000 rows.
If possible, limit the results you're working with (for example to a single horse).

Naturally, queried (and joined) feilds should be indexed.
Storing values (not necessarily in the same table) is the alternative.

When you say "I should be OK with the comparison" have you considered what I was saying regarding database use?
The expression used in the VBA function used directly in the query would perform worse.

Cheers.

 

[The Brown Growler]

Leigh,

I will create a table containing the current rating and previous rating per row using your 1st query. Once I have the new table I will create the comparison values +R... etc from that.

To run the query for individual horses would be a bit of a pain as there could be approx 10,000 individual horses each with approx 28 records. The numbers are very wide approximations, some horses have fewer records and other more records but it illustrates the general picture.

Rgds
Growlos