Results 1 to 4 of 4
  1. #1
    dinakar is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Nov 2017
    Posts
    1

    Exclamation Join tables with similar values in the primary key


    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

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,549
    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.

  3. #3
    Bulzie is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,511
    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.

  4. #4
    Micron is offline Very Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,369
    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.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 2
    Last Post: 04-26-2016, 05:24 PM
  2. Replies: 8
    Last Post: 08-25-2015, 08:06 AM
  3. How to Join Similar Fields Based on a Third Field?
    By Inaccessable in forum Queries
    Replies: 1
    Last Post: 07-12-2013, 03:56 PM
  4. Help with something similar to join?
    By lagunov in forum Queries
    Replies: 7
    Last Post: 10-28-2010, 07:01 AM
  5. UNION two tables with same primary key values
    By carillonator in forum Queries
    Replies: 1
    Last Post: 02-02-2010, 08:54 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums