General Betting
There is currently 1 person viewing this thread.
Hi I have a horse racing databse with a row for each horses performance. Typically the Table looks like this
Race date, Horse, Course, SP, Finishing Position, etc,etc, etc
I need only the horses last 10 performances returned as I disregard any performance > 10
Any help appreciated
Thanks
Grey Shot
Race date, Horse, Course, SP, Finishing Position, etc,etc, etc
I need only the horses last 10 performances returned as I disregard any performance > 10
Any help appreciated
Thanks
Grey Shot
sort by:
Show
per page
Replies: 50
|
By:
Grey Shot,
One possible way of doing this would be to sort the table by horse ascending then race date descending then use a RANKING function on the race dates per horse. The most recent race date per horse would be ranked 1, previous run would be ranked 2 and so on. Then once you have the race date rankings use a filter on the RANKING column where the value is |
|
By:
For some reason it truncated my message. to carry on, .....Then once you have the race date rankings use a filter on the RANKING column where the value is
|
|
By:
something odd going on here, it keeps truncating my replies
|
|
By:
Filter the query on ranking values
|
|
By:
Less than 11
|
|
By:
Dog appreciate your help but how would I write the Query? For Access2003 in SQL format?
|
|
By:
Dog, I currently export he performances into excel and do exactly what you say. I sort by Horse and then race date. Then I use the subtotal function ans any with > than 10 runs I just delete by hand.
Its a bit balls aching however |
|
By:
Have a look on the web for the access programmers forum, google for access forum and it should come up near the top with a dot UK domain name. They have a sub forum for dealing with access queries and if you search on Rank or Ranking you will get a good few topics. The access 2003 query needs to be an SQL query to use the RANK function, you cannot design it using the drag & drop of the query grid althought you can view the visual representation in the query grid.
|
|
By:
Grey Shot, The microsoft support knowledge based ID reference 208946 illustrates how to rank the records in an access query. I think the forum is deleting links to web pages so if it does not display after this then search microsoft support for that knowledge based ID. The link is:
http://support.microsoft.com/?kbid=208946 Rgds dog |
|
By:
Did I not give you a suggestion for this Grey Shot?
select top 10 * from RunnerTable where HorseID=XXXX order by RaceDate desc; Something like that. |
|
By:
Feck,
I might be wrong on this but if Grey Shot wishes to get the top 10 runs for more than 1 horse at the same time then the top 10 query will not provide this and will only work if 1 horse is selected. If a report or summary of an actual race is needed with all runners and their last 10 races then the filtered ranking query will do this. |
|
By:
Grey Shot,
Try the below SQL: SELECT T_RaceTableAlias.HorseName, T_RaceTableAlias.RaceDate, CStr((Select Count(*) from T_RaceTable Where [HorseName] = [T_RaceTableAlias].[HorseName] And [RaceDate] > [T_RaceTableAlias].[RaceDate])+1) AS Ranking INTO T_RaceTableRanked FROM T_RaceTable AS T_RaceTableAlias ORDER BY T_RaceTableAlias.HorseName, T_RaceTableAlias.RaceDate DESC; You will need to change the table and field names to match your own. In the above, the table is [T_RaceTable] the horse field is [HorseName] and the date of the race is[RaceDate] Paste this into the SQL window of an access query , rename and it will run OK, I have tested it and it works. Rgds dog |
|
By:
You can use Indexing of tables
1. Create a table named e.g. TodaysForm, Include a field named racenum (integer) 2. Append the form to TodaysForm 3. Create a 2nd table named e.g. horseracenum with horsename and and a new field named racenum (integer) 4. In the table properties, for the horsename field, change the indexed field to Yes(no duplicates) 5. Append the horses & dates to the horseracenum table by descending date (Appends the most recent formline) 6. Update the racenum field in the horseracenum table to 1 7. Update the racenum field in the TodaysForm table via a query linking both the horsename and racedates between the tables TodaysForm and horseracenum 8. Clear the horseracenum table 9. Append the horses & dates to the horseracenum table by descending date where racenum 1 10. Update the racenum field in the horseracenum table to 2 11. Update the racenum field in the TodaysForm table via a query linking the horsename and racedate between the tables TodaysForm and horseracenum repeat steps 8-11 (query 9 now has racenum 1 And 2, query 10 Updates racenum to 3) You end up with racenum of 1 to 10 for the 10 most recent formlines in the TodaysForm table |
|
By:
The forum has lost a few bits of text
In 9. is should be racenum is not 1 & should be a greater than sign followed by a less than sign then 1, with no spaces in between Similarly, under repeat steps 8-11 is not 1 or 2 & should be a greater than sign followed by a less than sign then 1 And a greater than sign followed by a less than sign then 2, with no spaces in between |
|
By:
If you need any help with thia, give me a shout
|
|
By:
All many thanks for the replies, I have been away until late this evening. Once I digest this I will come back.
Many thanks guys much appreciated. GS |
|
By:
I will post tomorrow guys still working on things
|
|
By:
Guys, for the purpose of simplicity ( I am simple) here is my current sql script for my query in Access 2010
At the moment it returns every horses performance going back to year dot in the database. I need it to give me each horses last 10 performances (or less if it has ran less than 10) hope you can help by re writing the script. I am very flakey on access so be gentle. The database is called "Races" SELECT races.[Race date], races.Track, races.Horse, races.Rating FROM races; |
|
By:
Sorry should have said access 2003
|
|
By:
Grey Shot - This should work if you add the following lines to your sql query.
ORDER BY races.[Race date] DESC TOP 10 |
|
By:
Mginvest thanks for replying . However Will that just return the last 10 races of all horse. i.e 10 records only?
|
|
By:
mginvest when added to the line I got an error " charecters found at end of sql statement"
|
|
By:
The TOP 10 bit doesnt go at the end thats reason for your error. Have a google for sub queries and "select last 10 access sql" etc
|
|
By:
brendanuk1 Could you write the query for me and amend the following
ORDER BY races.[Race date] DESC TOP 10 |
|
By:
Sorry I mean from this query
SELECT races.[Race date], races.Track, races.Horse, races.Rating FROM races; |
|
By:
Top 10 should go at start as feck said earlier
SELECT TOP 10 races.[Race date], races.Track, races.Horse, races.Rating FROM races ORDER BY races.[Race date] DESC This would give last 10 races for any horse though as has been said. SELECT TOP 10 races.[Race date], races.Track, races.Horse, races.Rating FROM races where race.horse = ?? ORDER BY races.[Race date] DESC Above would give you per horse, Dont think you are going to get it from a simple query so your going to need temporary tables or sub quesries. |
|
By:
Dogform says he has tested his query and it works. Do abit of learning about sql and access etc. You wont know what is good or bad solution otherwise and your data could be well messed up and you wouldnt know what was happening.
Good luck |
|
By:
Sorry Grey Shot. SQL syntax varies depending on what engine is using it. My db engine puts TOPs at the end of the sql query,but others have corrected it for you. I got most of it right for you though.[;)]
|
|
By:
Cheery, I will play with it later Many thanks
|
|
By:
Cheers I mean
|
|
By:
BTW Brendan is right. If you want the last 10 races for each individual horse then you will have to use the query he gave 2nd for each individual horse and devise another query to go through every horse you want to view.
|
|
By:
Grey Shot,
Paste the below SQL into the SQL query design window of your database. It has the table name and field names that you listed above and will provide the columns you require in a single query, also, there is no need to make changes to table or field names. Note that ranking queries can take some time to run so depending on the number of records in your table so it may not provide the result instantly, it might take a minute or so to run depending on the number of records to be ranked. One point to note is that I tested it and it works OK on a table where the actual table records themselves were sorted by horse name ascending and race date descending. It might be a good idea to sort your races table on that basis to absolutely guarantee it runs OK. SELECT racesAlias.Track, racesAlias.Horse, racesAlias.[Race date], racesAlias.Rating, CStr((Select Count(*) from races Where [Horse] = [racesAlias].[Horse] And [Race date] > [racesAlias].[Race Date])+1) AS Ranking FROM races AS racesAlias WHERE (((CStr((Select Count(*) from races Where [Horse] = [racesAlias].[Horse] And [Race date] > [racesAlias].[Race Date])+1)) |
|
By:
Grey Shot,
The forum truncated my reply again, the SQL in full is: SELECT racesAlias.Track, racesAlias.Horse, racesAlias.[Race date], racesAlias.Rating, CStr((Select Count(*) from races Where [Horse] = [racesAlias].[Horse] And [Race date] > [racesAlias].[Race Date])+1) AS Ranking FROM races AS racesAlias WHERE (((CStr((Select Count(*) from races Where [Horse] = [racesAlias].[Horse] And [Race date] > [racesAlias].[Race Date])+1)) |
|
By:
SELECT racesAlias.Track, racesAlias.Horse, racesAlias.[Race date], racesAlias.Rating, CStr((Select Count(*) from races Where [Horse] = [racesAlias].[Horse] And [Race date] > [racesAlias].[Race Date])+1) AS Ranking
FROM races AS racesAlias WHERE (((CStr((Select Count(*) from races Where [Horse] = [racesAlias].[Horse] And [Race date] > [racesAlias].[Race Date])+1)) |
|
By:
WTF is going on with this forum reply functionality, it keeps truncating replies.
|
|
By:
Grey Shot after the "WHERE>>>>" line also paste the below
ORDER BY racesAlias.Horse, racesAlias.[Race date] DESC; |
|
By:
SELECT racesAlias.Track, racesAlias.Horse, racesAlias.[Race date], racesAlias.Rating, CStr((Select Count(*) from races Where [Horse] = [racesAlias].[Horse] And [Race date] > [racesAlias].[Race Date])+1) AS Ranking
FROM races AS racesAlias WHERE (((CStr((Select Count(*) from races Where [Horse] = [racesAlias].[Horse] And [Race date] > [racesAlias].[Race Date])+1)) |
|
By:
Grey Shot,
Also at the end of the "WHERE...." line paste the following |
|
By:
the less than symbol ie a v pointing to the left followed by 11))
I think that some of the symbols in the code are being interpreted by the forum as control characters? |