Update query / Sql problem

[huey]

Here is my problem...

ID Name Odds as Decimal RaceEntry_Favs
--------- ------------------------- --------------- --------------
19940001 FREE DANCER 5.5
19940001 FRENDLY FELLOW 4.5
19940001 MCCARTHYS HOTEL 34
19940001 PANDESSA 9
19940001 SAOIRSE(IRE) 3.25
19940001 STREPHON(IRE) 8
19940001 WHEELER`S WONDER(IRE) 5.5
19940002 ALWAYS READY 8
19940002 GALLANT JACK(IRE) 21
19940002 HTHAAL(USA) 15
19940002 JUVENARA 34
19940002 MERRY NOTE 34
19940002 PRIDEWOOD GOLDING 21
19940002 REILTON 15
19940002 SALLY OF THE ALLEY 13
19940002 SKOLERN 13
19940002 TAKE TWO 1.6666666
19940002 TOSS THE DICE 6.5
19940002 WHAT A CARD 34
19940003 CASTLE DIAMOND 3
19940003 HARRISTOWN LADY 4
19940003 LAPIAFFE 7
19940003 PETTY BRIDGE 4.5
19940003 SINGING SAM 34
19940003 SIRRAH JAY 7
19940003 UPWELL 17

The above table has 350,000 records approx. What I am trying to do for my assignment
is create a new column (RaceEntry_Favs) and update it with an number depending on the starting position of the horse, i.e. 1 = favourite, 2= 2nd Favourite, 6 = 6th Favourite, etc...
So far I have tried (using the update query grid) Group By ID and MIN Odds as Decimal which then gives me this list for example:

19940001 SAOIRSE(IRE) 3.25
19940002 TAKE TWO 1.6666666
19940003 CASTLE DIAMOND 3

I am then trying to Update the RaceEntry_Favs column with 1.
At this point MS is telling me that this is not an updateable query?

Once this problem is solved then it would be easy to complete the rest as I would just repeat this problem but including RaceEntry_Favs IsNull to get the next lowest after 1 is complete to create the 2nd Favs and so on...

Can anyone help me...Tks

 

[Studentos]

Generally if you need to update your table on data results from complex query, you need to insert query results to temporary table with "insert" query and then create an "update" query using temp and main table only.

 

[huey]

Many thanks Studentos, I will give it a shout.

 

[huey]

Thanks for pointing me in the right direction...this is what I did.
I first created a make table query for three fields - ID (Group by), Odds as decimal (Min), RaceEntry_Favs (Group by and criteria Is Null). I then created an update query using the original RaceEntry_Favs field set to Is Null (just in case) and update set to "1", the other two fields for this query came from the temp table (ID and Odds as decimal) and the criteria was set as follows - [tblRaceEntry]![Race_ID] for the ID field and [tblRaceEntry]![Odds] for the Odds as decimal field. I now have all the smallest odds for each race set to 1 (Favourite), now because the RaceEntry_Favs is set to Is Null I can continue to run the Make table query to replace the last query and then run the update query to make the 2nd fav, and the 3rd fav and so on.
Hopefully this explaination can help others with a similar problem...
Once again a big thanks to Studentos.