help needed with UPDATE query 1

[teach314]

greetings - I need help with a query to UPDATE 'Set_Order' numbers in the table at LEFT to get the table at RIGHT. The Set_Order column shows the ranking of the Set_Value numbers (in ASC order) for each different value of Stage.

 [bold]
tbl_TEST   BEFORE UPDATING                        tbl_TEST   AFTER UPDATING
ID    Stage   Set_Value   Set_Order               ID    Stage   Set_Value   Set_Order  [/bold]
101     3       34                                101     3       34           2
102     3       89                                102     3       89           3
103     3        9                                103     3        9           1

104    12       44                                104    12       44           4
105    12       56                                105    12       56           5 
106    12       98                                106    12       98           6
107    12       16                                107    12       16           2
108    12        9                                108    12        9           1
109    12       34                                109    12       34           3

110    15       37                                110    15       37           2
111    15       16                                111    15       16           1

112    19  etc....                                112    19  etc....

Thank you for any assistance.
Teach314

 

[Andrzejek]

Can't you just create a recordset base on:

Select * From tbl_TEST
Order By Stage, Set_Value

and then go down the recordset and as long as you have the same Stage, update Set_Order to 1, 2, 3, ... etc

Have fun.

---- Andy

 

[dhookom]

It is typically a bad idea to store a value that can/should be calculated on the fly:

SELECT tbl_Test.ID, tbl_Test.Stage, tbl_Test.Set_Value, Count(tbl_Test_1.ID) AS SetOrder
FROM tbl_Test INNER JOIN tbl_Test AS tbl_Test_1 ON tbl_Test.Stage = tbl_Test_1.Stage
WHERE (((tbl_Test.Set_Value)>=[tbl_Test_1].[Set_Value]))
GROUP BY tbl_Test.ID, tbl_Test.Stage, tbl_Test.Set_Value;

Duane
Hook'D on Access
MS Access MVP