Access MS ACCESS, Compare two similar tables

[Alexander R.A]

Hi,
I need to compare to tables where there can be data not 100% identical:

We have two tables,Table1 and Table2 both with columns Product name and Producer.
Table 1 is the "master" table.
The data can be showed in table1 as:
Product name: AAA
Producer: GXL

In table the data can be shown as:
Product name: AAA or AAA_A or A_AAA
Producer: GXL

I need to find all AAA in table2 that exists in table1.

I have just started to use ACCESS and need someone that can please explain it for me in details

THX a lot, best regards Alex

 

[LynnTroubleshooter]

In the second table, make another field (NewField) to store the 3 letter code after stripping off the _A and A_ from the original table (OldField).
Make an update query to update NewField with the contents of OldField (basically, data backup).
Make an update query with a calculated field. BeginUnderscore:left([NewField],2) with a criteria of "*_*" In this query, you will update Newfield with a value of right([oldfield],3).
Make a second update query to check for ending underscores, just reverse the above.