Results 1 to 9 of 9
  1. #1
    HelenReed is offline Novice
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1

    Adding data from one Access sheet to another

    Hi



    I have a database with 625 records, I have been given another database that contains some new information relating to about 500 of these records that I need to add in as new columns. Both databases contain the unique identifying code for each record that could be matched. Is there a way access can do this?

    From what I can tell appending would add the information in as extra records on the bottom, I need them to become additional columns. Is this possible?

    I am not very experienced with access and would appreciate clear, step by step instructions. I have got myself very confused trying to read help sites!

    Thank you

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    add the new fields to the target table,
    make an update query, join the key field between the 2 tables,
    run update on the new fields.

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    or just use a query to join the two tables on the unique code - since only 500 records exist in the new data against 625 in the old, use a left join

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Quote Originally Posted by Ajax View Post
    or just use a query to join the two tables on the unique code - since only 500 records exist in the new data against 625 in the old, use a left join
    Umm ... INNER JOIN would work fine here
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Umm ... INNER JOIN would work fine here
    not if you want to see the fields of records in the old table where there is not a matching record in the new table

  6. #6
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    Obviously not, but from the description, that didn't seem relevant here....!
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I was going from

    that I need to add in as new columns

  8. #8
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    And I was assuming the OP could just use the two columns from the original table with no need to copy the data across ... which is what I thought you also meant.
    Anyway, shall we leave it now for the OP to decide whichever method she prefers ....
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

  9. #9
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Quote Originally Posted by HelenReed View Post
    I have a database with 625 records, I have been given another database that contains some new information relating to about 500 of these records that I need to add in as new columns. Both databases contain the unique identifying code for each record that could be matched.

    From what I can tell appending would add the information in as extra records on the bottom, I need them to become additional columns. Is this possible?
    Yes, this is possible. If you want the new data/fields added to the original table, instead of appending, you would use an UPDATE query.
    In broad terms, you would
    1) add (create) the new fields to the old dB table (the dB with the 625 records)
    2) link to the new dB/table (db table with 500 records)
    3) then run an update query
    4) last, delete the linked table.


    Without knowing the dB names, table names and field names, this is the best answer I can give.

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

Similar Threads

  1. Excel Sheet data import into Access
    By joesephb in forum Import/Export Data
    Replies: 5
    Last Post: 04-09-2018, 02:09 PM
  2. Replies: 5
    Last Post: 04-25-2017, 01:38 AM
  3. Replies: 11
    Last Post: 05-09-2014, 11:27 AM
  4. Replies: 1
    Last Post: 03-06-2013, 10:43 AM
  5. Import excel sheet data into Ms-access using VBA macros
    By gokul1242 in forum Import/Export Data
    Replies: 2
    Last Post: 10-02-2012, 04:39 AM

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