Join tables with similar values in the primary key

[dinakar]

Hi,

I have 2 tables where the primary key has similar but not exactly the same values. I am trying to run query by joining the 2 tables for the 'Name' column and returning the salary in the example below:



Please help. I Have 150 such rows. so individually setting 'LIKE' is very difficult.

Table 1:

Name	Address	City
Prince Junior +	83 tile avenue	pittsburg
vince taboo	22 mile road	harrisburg
matthew, eggert	41 willow	philadephia

Table2:

Name	2014 salary	2015 salary
Prince Jr.
Vince Tabo
MATTHEW Eggert

[ranman256]

Human names are the bane of databases. The have to be manually adjusted.
i added an extra field 'MATCHED' that fills in the common names that a query can actually match to.
the 'null' [matched] must then be viewed 1 at a time to see if they can match up.
I have side by side forms that i can kinda do some filtering and fill in the MATCH field.

[Bulzie]

As ranman256 said, you cannot match 100% successfully on names do to folks with same name or different variations of names as in your examples. Your best bet is to manually fix the names between tables, then add a unique employee ID or number to each record and use that to match going forward.

[Micron]

If you're just trying to amalgamate information from different sources, then I agree that you're faced with some manual work such as using the Matched concept provided.
If your intent is to keep this table structure, then you're on the wrong track. Names should not be used as primary key values, should not be duplicated in other tables and should be split as first and last names. The primary name table should use an autonumber id as the pk, and the pk values should be in other tables instead of the names. Alternatively, employee numbers that are never deleted, never edited and never duplicated make good pk values in lieu of autonumbers.