Modifying column in access

Question

I have 2 tables in MS Access, TableA and TableB. Table A has only 1 field: myFieldID, and TableB has only 1 field: myFieldName (In reality I have more fields, but these are the ones that matter for the sake of my problem). Both tables have records that mean the same thing, but written in a different, but similar way. For instances TableA has:

|TableA.myFieldId |
|-----------------|
|MM0001P          |
|HR0003P          |
|MH0567P          |

So as you can see all of the records are formated this way (with a P at the end): ([A-Z][A-Z][0-9][0-9][0-9][0-9]P) then, TableB has:

|TableB.myFieldName                          |
|--------------------------------------------|
|MH-0567 Materials Handling important Role   |
|MM-0001 Materials Management Minor Role     |
|HR-0003 Human Resources Super Important Role|

So this one has the format (without 'P' at the end): ([A-Z][A-Z]-[0-9][0-9][0-9][0-9] ([A-Z]|[a-z]*))

First, I would like to make join queries with tableA and tableB on these fields, but as you can see, results will be NULL every time since both fields have completely different records. So I would like to change every name in TableA.myFieldId with his corresponding name in TableB.myFieldName Problem is, that both tables have around 1 million records, and the fields are repeated multiple times in both tables, plus I don't know how to do this (MS Access doesn't even let me use Regular Expressions).

Answer 1

I would make a table (or query, if it changes often enough) of all unique entries in the 2nd table and the corresponding key for the 1st table. Then use that table or query to help join the two tables.

Something like

Select myFieldName as FName, left(myFieldName,2) & mid(myFieldName,4,4) & "P" as FID
from TableB
group by FName, FID

Important note - are all IDs found in both files, or do you have records in either table that are not in the other? If they don't always match, you may need additional logic or steps to make a master table from both tableA and tableB.