Results 1 to 2 of 2
  1. #1
    Kath81 is offline Novice
    Windows 10 Office 365
    Join Date
    Jun 2021
    Posts
    1

    Import data from Excel into Table columns only

    Hi All!
    Please excuse, but while I am a complete newbie to this site and to Access, I am trying to learn and have tried to find the answer via google and youtube for days on end - plus I asked an old work colleague but I cant find any information on how to do this.....
    My client has asked me to create a database of the devices we manage for them at their sites.
    Currently we use a very large Excel spreadsheet but it's massive and complicated.
    I've created the database using a template Access Inventory and it's working ok so far. The client has now asked for additional invoice information to be added to the database, so when I bill them, they can cross charge to an Oracle number. The problem is, I don't have the Oracle numbers in my database at the moment because this is new request - they are in a spreadsheet though. I need to attach an Oracle number to each "Project Number" which is in my Contacts Table. There are over 400 devices so I dont want to manually input the data. I have created the new field/column called "Oracle Number" in the contacts table and picked this up in my forms and reports. I know I can import data using a Linked table and then use an Append Query, but when i run this, the information is duplicated in the rows at the bottom of my Contacts table (so I ended up with 800 devices instead of 400).

    How do I import the Oracle number into a column with some sort of "lookup" against it's respective Project number?
    so my Excel Sheet looks like this....


    Project Number Site Name Address Oracle number
    45612 Building 3 London Road 2000004527

    and my contacts table is this...as you can see Column "Oracle number" is blank in the Contacts table and needs to fetch this info from the above Excel data.
    Project Number Site Name Address Oracle number
    45612 Building 3 London Road


    Like I say, I am a complete newbie to Access so I don't know how to code yet.
    Thank you.
    Kath

  2. #2
    ssanfu is offline Master of Nothing
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Hi and welcome to the forum..


    If ALL Excel Project Numbers are in the Access Contacts table, you don't want an APPEND query, you want to use an UPDATE query. Since you have an append query that works, just change it to a an update query. Tada!

    Suggestion: DO NOT use spaces in object names (fields, tables, queries, forms, reports).

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

Similar Threads

  1. Replies: 1
    Last Post: 01-19-2017, 10:13 AM
  2. Replies: 5
    Last Post: 04-26-2016, 05:30 AM
  3. Import Data from Excel when columns dont match
    By NickWren in forum Import/Export Data
    Replies: 3
    Last Post: 01-29-2016, 09:14 AM
  4. Replies: 4
    Last Post: 03-19-2015, 06:14 PM
  5. Import from excel when data is in different columns - beginner
    By faodavid in forum Import/Export Data
    Replies: 5
    Last Post: 08-03-2014, 12:04 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