How can I compare same field in 2 different tables when it match or likes?

I need to compare 2 tables (the field is code) and create a third when name match or likes (with results from each table).

table1: name surname code age

tab开发者_如何学编程le2: address city code

I need as results a new table with code from table1 and table2 to see the difference.

Each field are text like a345694s3.

i'm not sure but you can try with where condition .. ex: select * from tab1 t1, tab2 t2 where 't1.code'='t2.code'

If you get the result for the above code then make it as a subquery!!

If you're trying to see which codes are in table1 versus table2, try a FULL OUTER JOIN

SELECT t1.code AS T1, t2.code as T2
FROM   table1 t1
FULL   OUTER JOIN table t2
       ON t1.code = t2.code

Results:

T1     T2
====   ====
A      NULL  --'A' is in table1 but not table2
NULL   B     --'B' is in table2 but not table1
C      C     --'C' is in both tables

EDIT

As FULL OUTER JOIN is unavailable in MS Access, you can use INNER JOIN, LEFT OUTER JOIN, and RIGHT OUTER JOIN to achieve the same thing as discussed here.

From your comment you want to find any non-matching rows that imported badly. I would first find all the matching rows that imported correctly and then just select anything not present in that set.

SELECT * 
FROM table1 
WHERE code NOT IN (
    -- find all the matching values of code which imported correctly
    SELECT code 
    FROM table1 
      JOIN table2 ON table1.code = table2.code
)

This will not be a fast query but since it is for integrity checking it won't need to be run in production after the initial import so speed isn't the main issue.

SELECT u.iduser, a.idaccount, u.usercode_user, a.usercode_accounts
FROM users AS u, accounts AS a
WHERE (((a.usercode_accounts) Like "*" & [u].[usercode_user] & "*"));