If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Error msg when convertint a select query in to an update query
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 |
#2
|
|||
|
|||
Error msg when convertint a select query in to an update query
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. "Steve S" wrote: 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 |
#3
|
|||
|
|||
Error msg when convertint a select query in to an update query
This help?
ID ConID EsortKey Event LsortKey Level R1 30 0 solo 1 Novice R1 30 0 solo 2 Beginner "Jerry Whittle" wrote: 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. "Steve S" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|