Editing data in a column to match another

[agape234]

I have 2 tables. A column in each of these tables contains similar data but with a different syntax. (Table 1 Column B has numeric entries. Table 2 Column B contains the exact same data but with an alpha prefix) I want to eliminate the alpha characters so I can build a relations ship between the 2 tables. Can I do all this in a single query or report? Would it be more simple to edit one of the columns to match the other? If so, what is the easiest way to edit the column contents...

 

[GingerR]

Just write an update query to update table 2 to get rid of the 'alpha prefix'. then you're all set!

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

you can also do something like this:

         table1                  table2
Field1:  12345           Field2: A12345

SELECT * FROM Table1 INNER JOIN Table2 ON CLng(Table1.Field1) = CLng(Right(Table2.Field2, 2))

Leslie