SQL to update a table from values within same table

[Steve S]

I am trying to update a field in a table with the first corresponding value
in a small (30-40 records) subset of records in the same table.

Any and all help is appreciated

steve S

Before the update:
CID EsortKey Event LsortKey Level
R1 30 0 solo 1 Novice
R2 30 0 solo 2 Beginner
R3 30 0 solo 3 Intermediate
R4 30 0 solo 4 Advanced
R5 30 0 duet 5 Novice
R6 30 0 duet 6 Beginner
R7 30 0 strut 7 Novice
R8 30 0 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner

What I need after the update:
CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 5 duet 5 Novice
R6 30 5 duet 6 Beginner
R7 30 7 strut 7 Novice
R8 30 7 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner


I posted this request a few days ago and it was suggested I try the following:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That produced a “Type Conversion Error so I changed it to:

UPDATE Fees as F SET F.EsortKey = DMIN("LsortKey","Fees","Event = " &
'Fees.Event' & " and [CID] = 30")
WHERE F.[CID]=30;

That code kind of worked but what I now get is

CID EsortKey Event LsortKey Level
R1 30 1 solo 1 Novice
R2 30 1 solo 2 Beginner
R3 30 1 solo 3 Intermediate
R4 30 1 solo 4 Advanced
R5 30 1 duet 5 Novice
R6 30 1 duet 6 Beginner
R7 30 1 strut 7 Novice
R8 30 1 strut 8 Beginner
R9 27 0 solo 22 Novice
R10 27 0 solo 23 Beginner