S
Steve S
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
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