Inserting records comparing two tables

[knowledge76]

Hello friends,

I want to ask you experts how can I compare two tables and insert then the values into another table.

Table A
A B C
PK12 23 45

and Table B
A B C
12 23 45

how can i get the matches for column A of table A with PK in prefix and in table B without prefix "PK". I want to get the results in such a way that the number 12 which is lying at th end should be the matching criteria and PK which is infront should not be a matching criteria.
Thanks for your answers.

 

[FoFa]

What does the PK look like (I am assuming it is not literally "PK", that would be to simple)?
But on the outside chance it is actually "PK" you can do a RIGHT([A],LEN([A])-2)

 

[WayneRyan]

Knowledge,

I'm a little confused, but basically:

Insert Into TableC (FldA, FldB, FldC)
Select Mid(A.FldA, 3), A.FldB, A.FldC
From   TableA As A Inner Join TableB As B On
       Mid(A, FldA, 3) = B.FldA

This would work just as well:

Insert Into TableC (FldA, FldB, FldC)
Select B.FldA, A.FldB, A.FldC
From   TableA As A Inner Join TableB As B On
       Mid(A, FldA, 3) = B.FldA

Wayne

 

[knowledge76]

respect experts it works as perfect as anything could ever work.
thanks for your efforts.