Results 1 to 3 of 3
  1. #1
    Bobby100 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    13

    Import 2 excel tables and join with composite key?

    Hi, I recently had to match up 2 sets of records. Both sets were extracted from databases and imported into excel. So 2 separate excel tables.

    Both excel tables had a column for 'Route' and 'Mileage'. Both tables were plotting company assets, which were located by their mileage distance along a given route.

    As the 2 excel tables were identifying the same assets but came from 2 different databases which had different additojal fields I had to link the 2 tables together.

    To do this I imported the excel tables into Access. I then went into design view and simply joined route from table 1 to route on table 2, then did another join, connecting mileage on table 1 to mileage on table 2.

    I then displayed the results. So if route and mileage was the same it would join the records from both tables into a new record that has effectively matched them together.

    My question is whether this was the right way to go about joining the tables? Should I have created a composite key in one/ both tables and joined this way?

    Thanks,

    Rob.

  2. #2
    CJ_London is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,398
    no need for a composite key, the way you are doing it is OK. However, composite key or not) this may leave records in both tables which should considered the same but don't because they are not an exact match on the keys. Also, I would have thought you would also be matching on the company asset as well.

    To obtain a list of these from each table use left or right joins (depending on which way you are joining them). You can filter out the matching records by using an 'is null' criteria on one of the fields on the 'other end' of the join

  3. #3
    Bobby100 is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2016
    Posts
    13
    Thanks for the reply Ajax.

    Yeah I only wanted exact matches for now. Those where mileages are slightly different will be looked at individually to see if they match up as mileages may be slightly different between the two tables.

    This also applies to the asset name as they have been recorded differently in the 2 tables.

    I was basically doing this to filter out the records that were an exact match so that I could concentrate on those where the matching involved more discretion.

    Thanks,

    Rob

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

Similar Threads

  1. Import Excel Sheet to Update Tables
    By guillermoftw in forum Access
    Replies: 4
    Last Post: 01-13-2015, 09:23 AM
  2. Replies: 3
    Last Post: 03-06-2014, 03:53 PM
  3. Import Excel Data to Multiple Access Tables
    By colby in forum Import/Export Data
    Replies: 3
    Last Post: 11-04-2011, 12:17 PM
  4. Outer Join Composite Key
    By Lady_Jane in forum Queries
    Replies: 6
    Last Post: 08-26-2011, 09:44 AM
  5. Automate Excel Import to Access Related Tables
    By KramerJ in forum Programming
    Replies: 6
    Last Post: 04-04-2009, 04:24 PM

Tags for this Thread

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