Update Query where fields not quite the same

  • Thread starter Thread starter Ali
  • Start date Start date
A

Ali

I have a field stockcode that needs to be linked to a field in another
table called New Stockcode. Some of the stockcodes are the same but others
only the first 6 characters are the same.

eg
Table 1 Table 2
Stockcode NewStockcode
12345/01 12345/01
300000 300000 ABCD

Is it possible to join the two tables using a Like Stockcode* sort of logic

If yes, how?
 
Hi,

I presume you are talking about joining these tables in a query.
The simplest way would be to create a query for each table which returns
all relevant fields and a calculated field: Left(Stockcode,6).
Then use these queries as the datasource in another query and just drag
a link between both fields.

Regards,
Andreas
 
just as you say, using the Like operator.

Select Table1.StockCode, Table2.NewStockCode from table1, table2 where
table2.NewStockCode like table1.stockcode & "%"

bear in mind you will probably get multiple matches. .. ..

ie
Table 1
Stockcode
300000

Table 2
NewStockcode
300000 ABCD
300000 DEFG
3

Result
Stockcode NewStockcode
300000 300000 ABCD
300000 300000 DEFG
300000 3
 
Hi,

Still with a possibility to have multiple match, but to force to be either
fully equal or to have at least the first 6 characters equal:


SELECT a.*
FROM a, b
WHERE a.stockCode = b.StockCode
OR Left(a.StockCode, 6) = Left(b.StockCode, 6)


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top