S
Steve S
I posted this yesterday , got one post for additional infor, posted update,
and then nothing. I would like to try again since I really do need to get
this done. thanks in acvance anr any help.
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.
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
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
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.[CID])=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.[CID])=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
and then nothing. I would like to try again since I really do need to get
this done. thanks in acvance anr any help.
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.
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
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
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.[CID])=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.[CID])=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