Access Query help required

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

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

something odd going on here, it keeps truncating my replies

Filter the query on ranking values

Less than 11

Dog appreciate your help but how would I write the Query?  For Access2003 in SQL format?

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

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.

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

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.

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.

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

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

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

If you need any help with thia, give me a shout

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

I will post tomorrow guys still working on things

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;

Sorry should have said access 2003

Grey Shot - This should work if you add the following lines to your sql query.

ORDER BY races.[Race date] DESC
TOP 10

Mginvest thanks for replying . However   Will that just return the last 10 races of all horse. i.e 10 records only?

mginvest when added to the line I got an error " charecters found at end of sql statement"

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

brendanuk1  Could you write the query for me and amend the following

ORDER BY races.[Race date] DESC
TOP 10

Sorry I mean from this query

SELECT races.[Race date], races.Track, races.Horse, races.Rating
FROM races;

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.

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

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.[;)]

Cheery, I will play with it later  Many thanks

Cheers I mean

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.

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))

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))

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))

WTF is going on with this forum reply functionality, it keeps truncating replies.

Grey Shot after the "WHERE>>>>" line also paste the below
ORDER BY racesAlias.Horse, racesAlias.[Race date] DESC;

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))

Grey Shot,

Also at the end of the "WHERE...." line paste the following

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?

Grey Shot,

I am going to try this reply using the forum Beta version to see if I can get round the truncating of the replies. The code 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))

Nope, still truncated it. You should be able to reconstitute it all by adding the extra line that begins with "ORDER....below the "Where.... line and then adding the less than symbol ie a v pointing to the left followed by 11)) at the end of the "Where... line after the +1)) bit

I think the less that symbol, ie the sideways v pointing to the left is the character causing the truncating.

Rgds
dog

html entities removed ie lt gt etc

All many thanks, I  have not been in much over the wekend so will digest asap and give feedback

Thanks for all the replies

Buy a copy of this book, it is very good. I'm finding it tough going, but the book is very well written.
http://www.amazon.co.uk/gp/offer-listing/0782123244/ref=sr_1_1_olp?ie=UTF8&s=books&qid=1294655086&sr=8-1&condition=used
Grey Shot, did you understand the method I suggested? You can do this with a series of queries (put together in a macro will reduce it to a double click, or a single click if you put a button tied to run the macro on a form.

I am currently working on my own web based formbook. I have nearly finalised it. I generate a web page for each race result and one with each horses form. I have all manner of stats and hyperlinks, inlcuding hyperlinks to play all the races from the pages. The horses pages link to the results pages and vice versa. I generate my speed ratings each day and have hyperlinks to the form pages for every horse. I believe my web based form book to be better than the racing post's and ATRs!

^ That may be a slight exageration

Guys, with the truncating I am getting confused , I told you I was a novice. However if you want to copy it into a comms

gt09876

Yahoo

I am based in the UK

Thanks

Stowe, very interesting I am trying do do something similar but this problem keeps rearing its head with past number of performances. Once I over come this problem I then need to run my ratings historically to find the effectiveness of them

Also thanks dogform for constantly trying to help.  Hope to hear  from you all soon

Thanks  again

Grey Shot,

Sent you SQL via email to what I interpreted as your email address from your reply.


Rgds
dog

Dogform,  its official, you are a genius .  Many thanks and also to all the posters who will enable me now to sleep at night

Many thanks