Mapping values in one table column to another table column

[magikminox]

Hi Guys

I have a table that contains codes for commodities.Some of the codes in this table have changed and some of them have not.So now i want to design a solution that enable me to map the new codes in a different mapping table to the old ones in the other table.I also want to retain the old codes because most of the archived data used the old codes.

Where there is no new code, the current code is being retained.How do i design my table and queries so that i can use the new codes as if i was using the old code.I want to select products with a certain code but using the new code and mapping to the old codes or vice versa.

Please help.
The structure of the data is like this.
Code Name
AA AA
AL Aluminium
ALM ALM
ALT Aluminium in tonnes
AR AR
AUD Australian Dollars
AUJPY AUJPY
CAQ CAQ
CC CC
CCF CCF
CER Carbon Emmission Reduction

The mapping table is like this:
XAA AA
XAL AL
XMA ALM
XAL ALT
XAR AR

Thank you.

[Delerna]

I sort of understand your question but not enough to offer a solution.
Can you offer more explanation, particularly the mapping table