Append additional characters to a text field 1

[EDGE99]

I have a table that has a product ids. All of these product ids are now changing to have two sometimes three additional characters at the end. How do I append chars to the end of an existing field using an access query.

Product id
-----------
abcd12
bcde13
cdef14

New Product id:
---------------
abcd12-E3
abcd12E3
bcde13-E3
bcde13E3

 

[Remou]

In SQL view, the query would look something like:

[tt]Update [Name of Table] Set [Product ID]=Trim([Product ID]) & "-E3"[/tt]

But this will update all products to the new name and it cannot be undone.

 

[EDGE99]

Remou,

I started thinking it might be better if I didn't change my product codes but instead make my query more robust to handle this.

I have tried the following but can't get it to work.

table 1 = transactions
This table can contain product codes with the extra chars or they could be without the extra chars

Table 2 = list of product codes I want to use as search criteria. These codes do not have the extra chars.

[Table 1] ----> [Table 2] Left Join on Product Code

So could I just put in the criteria field in my query for the product code of the [table 1] to state:

Like [Table 2.ProductCode]? also tried * but gives me an error when I try and get out of the criteria field.

Any ideas?

So it would match

 

[Remou]

If you join the two tables in that way, the fields need to match. You can switch to SQL view and paste this into the window:

[tt]SELECT Table1.ProductCode, Table2.ProductCode
FROM Table1 INNER JOIN Table2 ON left(Table1.ProductCode,3) = left(Table2.ProductCode,3);[/tt]

To see if it is near to what you want. You will not be able to view this query in design view.

 

[EDGE99]

But won't this only compare the last three chars of the product codes.

What I want to do is leave my product codes as is and still use it to query against the transaction database but pull all product codes that either equal to what I have or equal to what I have and the new extentions.

Transaction Table
-----------------
12345
23456
34567
12345E3
34567-E3
67890

My product Table
----------------
12345
34567

When I query the transaction table I want to check to see if the product code is equal to my product table and any product code that has the extra characters.

So my end results to be:

12345
12345E3
34567
34567-E3

I am not sure how to accomplish this.

 

[Remou]

I did not quite get what you wanted. Try this:

[tt]SELECT [Transaction Table].ProductCode
FROM [Transaction Table], [Product Table]
WHERE [Transaction Table].ProductCode Like [Product Table].ProductCode & "*";[/tt]

 

[PHV]

My way:
SELECT T.ProductCode
FROM [Transaction Table] AS T
INNER JOIN [Product Table] AS P ON Left(T.ProductCode,Len(P.ProductCode))=P.ProductCode

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886