Error msg when convertint a select query in to an update query

[Guest]

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

Before the update:
R1 0 solo 1 Novice
R1 0 solo 2 Beginner
R1 0 solo 3 Intermediate
R1 0 solo 4 Advanced
R1 0 x-strut 5 Novice
R1 0 x-strut 6 Beginner
R1 0 strut 7 Novice
R1 0 strut 8 Beginner

What I need after the update:
R1 1 solo 1 Novice
R1 1 solo 2 Beginner
R1 1 solo 3 Intermediate
R1 1 solo 4 Advanced
R1 5 x-strut 5 Novice
R1 5 x-strut 6 Beginner
R1 7 strut 7 Novice
R1 7 strut 8 Beginner

The following select query displays the desired results

SELECT Fees.Event, Fees.EsortKey, First(Fees.LsortKey) AS NewKey
FROM Fees
GROUP BY Fees.Event, Fees.EsortKey, Fees.[Contest ID]
HAVING (((Fees.[Contest ID])=30));

But when I change to an update query (as shown) I get the error shown after
the SQL.

UPDATE Fees SET Fees.EsortKey = First([NewKey])
WHERE (((Fees.[Contest ID])=30));


“You tried to execute a query that does not include the specified expression
‘EsortKey’ as part of an aggregate functionâ€

How do I modify the select query into a valid update query

Any and all help is appreciated

Steve S

 

[Guest]

Steve,

What are the names of the columns. Kinda hard to guess.

Also I don't see any data in your examples where a value is =30

 

[Guest]

This help?

ID ConID EsortKey Event LsortKey Level
R1 30 0 solo 1 Novice
R1 30 0 solo 2 Beginner

Steve,

What are the names of the columns. Kinda hard to guess.

Also I don't see any data in your examples where a value is =30
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

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

Before the update:
R1 0 solo 1 Novice
R1 0 solo 2 Beginner
R1 0 solo 3 Intermediate
R1 0 solo 4 Advanced
R1 0 x-strut 5 Novice
R1 0 x-strut 6 Beginner
R1 0 strut 7 Novice
R1 0 strut 8 Beginner

What I need after the update:
R1 1 solo 1 Novice
R1 1 solo 2 Beginner
R1 1 solo 3 Intermediate
R1 1 solo 4 Advanced
R1 5 x-strut 5 Novice
R1 5 x-strut 6 Beginner
R1 7 strut 7 Novice
R1 7 strut 8 Beginner

The following select query displays the desired results

SELECT Fees.Event, Fees.EsortKey, First(Fees.LsortKey) AS NewKey
FROM Fees
GROUP BY Fees.Event, Fees.EsortKey, Fees.[Contest ID]
HAVING (((Fees.[Contest ID])=30));

But when I change to an update query (as shown) I get the error shown after
the SQL.

UPDATE Fees SET Fees.EsortKey = First([NewKey])
WHERE (((Fees.[Contest ID])=30));

“You tried to execute a query that does not include the specified expression
‘EsortKey’ as part of an aggregate functionâ€

How do I modify the select query into a valid update query

Any and all help is appreciated

Steve S