Results 1 to 5 of 5
  1. #1
    octsim is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    15

    Import Excel data into existing Access Table

    Hello,



    so I've got a table with around 40 fields - half of them I want to enter manually using a data entry form which I've already created. The other half already is in a table from Excel, which I've successfully imported. The field names that need to be filled in the original "main" table are identical to those of the excel file I have imported. From what I've read, I need to make a query in order to add data from one table to another - is that correct? There's one field that both tables contain - something like a serial number. What I want is to check for the serial number and if it's the same, add the data that is missing (the other 20 fields who have the same names).
    Can anybobdy point me in the right direction?

    Thanks in advance!

    Octavio

  2. #2
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    Yes, you would use an Update Query to do that (and Update Query updates existing records in a table).
    See here for details: http://office.microsoft.com/en-us/ac...010076527.aspx, specifically the "Update data from one table to another" section.

  3. #3
    octsim is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    15
    First off, thanks for the quick reply! I've already read that article but it's too unspecific to understand how it really works. I've followed the steps until where I select what I want to update (every data entry). I know how to select the fields that I want to update. What I don't understand is, how do I tell the query to look for the same serial number and add the fields accordingly? How do I select the source?
    I have a main table and a source table. The main table contains around 40 fields. The source table only contains 20 fields that I want to update into the main table. Both contain one field they should use to identify each other (serial number). I changed the query to Update but I don't know how to link the serial numbers together.

  4. #4
    JoeM is offline VIP
    Windows XP Access 2007
    Join Date
    Jun 2012
    Posts
    3,904
    First, you create a simple select query, like any other query. So you want to add BOTH your tables, and join/link them on your serial number field.
    Then, you only select the fields that you want changed to display in you query.
    Then, change the query type to Update, and it will give you a new row in the Query builder for "Update To".
    Here, you select the corresponding fields from your source query (the values you want to pass into the other table).

    That is all there really is to it!

  5. #5
    octsim is offline Novice
    Windows 7 64bit Access 2010 64bit
    Join Date
    Oct 2013
    Posts
    15
    Got it now, didn't see I only had to drag n drop the serial number field into the other one. Now it works perfectly, thanks a lot!

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

Similar Threads

  1. Replies: 5
    Last Post: 05-09-2013, 06:47 PM
  2. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  3. Replies: 3
    Last Post: 12-14-2012, 11:26 AM
  4. Import data from Excel to table on click
    By mrbabji in forum Import/Export Data
    Replies: 1
    Last Post: 04-19-2012, 05:15 PM
  5. Importing Excel data to an existing table
    By tonyrhills in forum Import/Export Data
    Replies: 3
    Last Post: 12-23-2011, 09:19 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