434,652 Members | 1,842 Online
Need help? Post your question and get tips & solutions from a community of 434,652 IT Pros & Developers. It's quick & easy.

# Partial matching

 P: n/a Hello I have 2 tables: table1 CODE | PRICE 1234 | 20 234 | 10 table2 CODE | PRICE 1234a | 1234b | 1234c | 234a | 234b | 234c | 234d | WHat I need to do is, compare the string for CODE in table 1 against what is found in table2. The relationship is always the same in that table2.code is always what is found in table1 plus one more character. SO I need to update table2 based on that so the result is: 1234a | 20 1234b | 20 1234c | 20 234a | 10 234b | 10 234c | 10 234d | 10 I'm kind of a n00b, but I need to use the MID function perhaps? Any help greatly appreciated. Mar 1 '07 #1
7 Replies

 P: n/a On Mar 1, 5:34 pm, NOS...@comcast.net (Tony V) wrote: Hello I have 2 tables: table1 CODE | PRICE 1234 | 20 234 | 10 table2 CODE | PRICE 1234a | 1234b | 1234c | 234a | 234b | 234c | 234d | WHat I need to do is, compare the string for CODE in table 1 against what is found in table2. The relationship is always the same in that table2.code is always what is found in table1 plus one more character. SO I need to update table2 based on that so the result is: 1234a | 20 1234b | 20 1234c | 20 234a | 10 234b | 10 234c | 10 234d | 10 I'm kind of a n00b, but I need to use the MID function perhaps? Any help greatly appreciated. If this is a *one-time* update then the following seems to work: UPDATE Table1 INNER JOIN Table2 ON Table2.Code LIKE Table1.Code & "?" SET Table2.Price = [Table1].[Price]; However, if this is an ongoing thing then fix the structure of Table2 by splitting off the last character, e.g. CODE SUBCODE 1234 a 1234 b 234 a Mar 1 '07 #2

 P: n/a On Mar 1, 5:34 pm, NOS...@comcast.net (Tony V) wrote: Hello I have 2 tables: table1 CODE | PRICE 1234 | 20 234 | 10 table2 CODE | PRICE 1234a | 1234b | 1234c | 234a | 234b | 234c | 234d | WHat I need to do is, compare the string for CODE in table 1 against what is found in table2. The relationship is always the same in that table2.code is always what is found in table1 plus one more character. SO I need to update table2 based on that so the result is: 1234a | 20 1234b | 20 1234c | 20 234a | 10 234b | 10 234c | 10 234d | 10 I'm kind of a n00b, but I need to use the MID function perhaps? Any help greatly appreciated. This should work if both code fields are of a string type. UPDATE Table1, table2 SET table2.Price = [table1].[price] WHERE (((Left([table2].[code],Len([table1].[code])))=[table1]. [code])); If Table1 is a number field UPDATE Table1, table2 SET table2.Price = [table1].[price] WHERE ((Left([table2].[code],Len(CStr([table1].[code])))=CStr([table1]. [code]))); Mar 1 '07 #3

 P: n/a Hi Tony, Well, in Tsql (the sql for sql server) you can do this: Select t1.* From Table1 t1 Inner Join Table2 t2 On t1.Code = Left(t2.Code, Len(t2.Code)-1) But this is not supported in Jet Sql (sql for Access). One Alternative would be to create an additional table which would link these two table -- say Table3 Table3 would contain these fields: Code1, Cod2 Code1, Code2 1234 1234a 1234 1234b 1234 1234c 1234 1234d 234 234a 234 234b 234 234c 234 234d Now the sql statement looks like this: select t1.*, t2.Code From (Table1 t1 Inner Join Table3 t3 On t1.Code = t3.Code1) Inner Join Table2 t2 On t3.Code2 = t2.Code Jet sql does support this statement. It may be a little tedious, but it is a workaround. Rich *** Sent via Developersdex http://www.developersdex.com *** Mar 1 '07 #4

 P: n/a NO****@comcast.net (Tony V) wrote in news:98*******************@216.196.97.136: Hello I have 2 tables: table1 CODE | PRICE 1234 | 20 234 | 10 table2 CODE | PRICE 1234a | 1234b | 1234c | 234a | 234b | 234c | 234d | WHat I need to do is, compare the string for CODE in table 1 against what is found in table2. The relationship is always the same in that table2.code is always what is found in table1 plus one more character. SO I need to update table2 based on that so the result is: 1234a | 20 1234b | 20 1234c | 20 234a | 10 234b | 10 234c | 10 234d | 10 I'm kind of a n00b, but I need to use the MID function perhaps? Any help greatly appreciated. You would be better off using the LIKE operator. Practice this on a copy of the database to make sure you get it working correctly Create a new query. Add both tables. Double click on each of the table 2 fields to move them into the lower panel of the query builder. Put this in the Criteria row for the code column: Like [table1].[code] & "*" the * says to match any character. Change the query to an update query. Put this in the Update To: row of the Price column: [table1].[price] Note: If you have 123a and 1234a and 12345a these will all match 123 from table 1. You will need to try something like Like [table1].[code] & "[A-Z]*" the [A-Z] says only match characters in the range from A to Z. -- Bob Quintal PA is y I've altered my email address. -- Posted via a free Usenet account from http://www.teranews.com Mar 2 '07 #5

 P: n/a Rich P

 P: n/a Select t1.* From Table1 t1 Inner Join Table2 t2 On t1.Code = Left(t2.Code, Len(t2.Code)-1) I tried this in the Query Sql view but it complained anyway. How can you get it to run? I must be missing something, maybe some [ ] square brackets? Rich *** Sent via Developersdex http://www.developersdex.com *** Mar 2 '07 #7

 P: n/a Rich P

### This discussion thread is closed

Replies have been disabled for this discussion.