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
|
|||
|
|||
Update query trouble
I want to copy the CodeID value from one record to another, in the same
table, to a certain record where a part of the Doc_Number (for the record with the CodeID) is found in the Comment field (for the record with a blank CodeID). Table Example: ID CodeID Doc_Number Comment 23 16 DCS01234.jpg Yada, yada, yada. 55 0 DCS09876.jpg Blah, blah, blah. 1234. The result should copy the 16 from record 23 into the CodeID field for record 55. This is because the "1234" four-digit string before the file extension was found somewhere in the Comment field of the other record. Here's what I have tried so far: UPDATE tblObservations SET tblObservations.CodeID = "CodeID" WHERE (((tblObservations.Comments) Like Left(Right("Doc_Number",8),4))); I'm on the wrong track . . . |
#2
|
|||
|
|||
Update query trouble
On Wed, 15 Oct 2008 16:47:00 -0700, bhammer
wrote: I want to copy the CodeID value from one record to another, in the same table, to a certain record where a part of the Doc_Number (for the record with the CodeID) is found in the Comment field (for the record with a blank CodeID). Table Example: ID CodeID Doc_Number Comment 23 16 DCS01234.jpg Yada, yada, yada. 55 0 DCS09876.jpg Blah, blah, blah. 1234. The result should copy the 16 from record 23 into the CodeID field for record 55. This is because the "1234" four-digit string before the file extension was found somewhere in the Comment field of the other record. Here's what I have tried so far: UPDATE tblObservations SET tblObservations.CodeID = "CodeID" WHERE (((tblObservations.Comments) Like Left(Right("Doc_Number",8),4))); I'm on the wrong track . . . What if several records have a match? You could try UPDATE yourtable AS X SET X.CodeID = (SELECT CodeID FROM yourtable AS Y WHERE Y.Comment LIKE "*" & Mid(X.DocNo, 5, 4) & "*") but I rather doubt it will be an updateable query because of the multiple matches issue; you may be able to use UPDATE yourtable SET codeID = DLookUp("[CodeID]", "yourtable", "Comment LIKE *" & Mid(DocNo, 5, 4) & "*") to get just the first matching comment. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|