Partial matching

[Tony V]

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.

[Gord]

On Mar 1, 5:34 pm, [e-mail] (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

[storrboy]

On Mar 1, 5:34 pm, [e-mail] (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])));

[Rich P]

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 ***

[Bob Quintal]

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

[Bob Quintal]

Rich P <rp*****@aol.comwrote in
news:45*********************@news.qwest.net:

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

Actually it is supported in Jet SQL. but you must enter it in the
SQL view of the query builder. And NEVER open the query in design
view, because the query builder will protest then delete the join.
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[Rich P]

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 ***

[Bob Quintal]

Rich P <rp*****@aol.comwrote in
news:45*********************@news.qwest.net:

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 ***

I just pasted it into the SQL View of Access 2002 and adjusted the
table names. It ran fine..

What was Access complaining. Could you have a data type mismatch
between your code columns?
--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com