I maintain a database of UK horseracing results, and I attach a sample section of the database (file [url removed, login to view]) sorted by horse name. The full database comprises over 11,000 rows such as the 33 shown in the sample section, and of course is growing steadily.
When analysing a current race I use an Excel sheet as per file B.xls.
Part of the data I need when analysing a current race is in the database, and I would like to be able to copy that data automatically into the Excel sheet. Specifically, once I have entered the list of runners in the first column of the sheet (in the example I have entered Ace of Hearts), I would like to be able automatically to get up to nine pieces of data for each horse from the database and placed in the relevant cells of the sheet, as shown.
The data to be copied is from the entries for the three most recent runs of the horses in question (dates in the database are in UK form - dd/mm/yy). It comprises that in three cells for each race, those in the database columns headed "Cl", "Dis +/-" and "OR".
The data for the most recent race needs to be copied into the relevant "Lto class", "Lto dist." and "Lto OR" columns of the sheet, that for the 2nd most recent race into the "2nd class", "2nd dist." and 2nd OR" columns, and that for the 3rd most recent race into the "3rd class", "3nd dist." and "3rd OR" columns (as per the Ace of Hearts illustration).
A complication is that not all the horses listed in the sheet will have entries in the database, and those that do will not always have three. In these cases the relevant cells in the sheet need to be left empty. (Obviously, where a horse has only two entries in the database, the relevant data for the more recent needs to go into the relevant "Lto class", "Lto dist." and "Lto OR" cells of the sheet, etc.)
If this can be done, it needs to be simple for the user to achieve when analysing a new race.
The database and sheet are Excel 2000.
I hope my aim is clear from the above and the two files, but would be happy to clarify as necessary.