Edit Question - KnowledgeHub

Title Body

I have a select query which does some text manipulation to essentially reformat a field so that I can look it up in another table: If my first table if I have a field like "J1/2" it looks up the ID of a record in a different table with J1 and J2 in the appropriate fields. This all works well. Now I want to update the original table so I don't have to do lookups using this string manipulation anymore, but my attempts at update queries end with "Operation must use an updateable query" Any ideas? My SELECT statement: SELECT DISTINCT t1.DD, t1.TN, t1.DD & " J" & MID(t1.TN,2,1) AS CalculatedStart, t1.DD & " J" & MID(t1.TN,4,1) AS CalculatedEnd, t2.ID FROM t1 INNER JOIN t2 ON (t1.DD & " J" & MID(t1.TN,2,1)=t2.StartLink) AND (t1.DD & " J" & MID(t1.TN,4,1)=t2.EndLink) WHERE t1.TN Like "J?/?" AND t1.DD Like "M*"; Recall - this works fine and I get the necessary t2.ID out the other end. So I want to do something like: UPDATE t1 SET t2ID = ( SELECT Query1.ID FROM Query1 WHERE t1.DD=Query1.DD AND t1.TN=Query1.TN ) WHERE t1.TN Like "J?/?" AND t1.DD Like "M*"; Only this fails. This is within MS Access itself so I can't imagine an actual permissions problem like most of the "Operation must use an updateable query" problems seem to be. EDIT: Trying to simplify the case that doesn't work. This UPDATE query is fine: UPDATE t1 SET t2ID="Unknown" WHERE TN LIKE "J?/?" AND DD LIKE "M*"; This one fails (Thanks Goedke - this example obviously fails because the subquery returns more than 1 result. I had oversimplified to try to find my problem) UPDATE t1 SET t2ID=(SELECT ID FROM t2) WHERE TN LIKE "J?/?" AND DD LIKE "M*"; So do I just have my subquery syntax wrong in some way? EDIT: This SELECT statement is fine too: <cod

Tags (comma-separated) Cancel