New to Access 2013 - Need help updating database using excel import

I am trying to learn Access (newbie here), so please answer as if answering a newbie.
(Access Table with updates from Excel)

My current table has about 15 columns, my "key" is "APP ID", but when I want to update my database all the other columns could change.

Here are some of the columns I am using:
APP ID (Key)

Every day I need to update this table.  There are always completely APP ID's, but there are also changes to old ones.

For example:
App ID "1" may change it's status from open to closed, the group may change from Tier 1 to Tier 2.

How do I update the old record in a database?
What I would like to do is find the duplicates and rewrite the old data with the new data, and ultimately end up with one table with the most up to date data.

Thanks in advance for your help.
Who is Participating?
PatHartmanConnect With a Mentor Commented:
If it is two years old, how have you been updating it during that time?

When you can't replace the table, then you have to use update and append queries as Rey has suggested.  If you are not familiar with SQL, then you should use the QBE to build your queries.

..Add the destination table to the grid.
..Add the update table to the grid.
..Draw a join line between the unique ID.
..Change the join type to RIGHT.  Yes, Right.  That translates to select all rows from the update table and matching rows from the destination table.  This is a trick that will allow you to combine the add/update into a single query.  Due to the right join, the query will actually create records in the destination table when no match is found.
..Change the query type to Update
..Double click to select each column in the destination table (except the autonumber if you have one)
..If the update table has matching column names, Access will automatically populate the "Update To" line.  If the column names are different, you will have to manually enter the source column names.  Use the format: tblUpdate.ColName.  Use square brackets if you have used non-standard column or table names.
..Save the query.
..Run the update (you did back up first didn't you?)

Deletes are a different problem.  Let us know if you have to handle them.
Rey Obrero (Capricorn1)Commented:
1. Import the excel file to a temp table  "tmpTable"
   here is the code to import the excel file

   docmd.transferspreadsheet acimport, , "tmpTable", <path and name of the excel file>, true

2. to update old records and add new records to your table, use an update query with a LEFT join

    Update yourTable Left join tmpTable On yourTable.[APP ID]=tmptable.[APP ID]
    Set yourtable.Technician=tmpTable.Technician,

            other fields here
rnhturner1Author Commented:
New to ACCESS -  I am sure that is a perfect answer but I am not sure where to put that code?

If someone has time, I need a paint by numbers approach.
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Do you update the data via other means once it is imported?  If you never update it after you import it, simply delete it and import a new version.  Compact after you do this to remove dead space.
Rey Obrero (Capricorn1)Commented:
correction in the update query

 Update tmpTable Left join yourTable On tmptable.[APP ID]=yourTable.[APP ID]
    Set yourtable.Technician=tmpTable.Technician,
rnhturner1Author Commented:
"Do you update the data via other means once it is imported?  If you never update it after you import it, simply delete it and import a new version.  Compact after you do this to remove dead space."

It is a database that is over 2 years old and only the last two months is updated as the file is way too large to pull and import daily.  It is not too large for ACCESS, but the program that gathers the data can't handle the load to get the data all at once.
Rey Obrero (Capricorn1)Commented:
rnhturner1Author Commented:

Is there a way to just update all fields that are associated with that one APP ID?
No.  You have to specify each column you want to update.  As long as the column names are the same, using the QBE as I described is pretty quick since you select all the fields by double clicking and Access will automatically map them.  It obviously gets more tedious if you have to map field by field because the names are different.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.