Solved

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

Posted on 2015-01-06
9
155 Views
Last Modified: 2015-01-19
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)
Technician
Status
Group

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.
0
Comment
Question by:rnhturner1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
  • 3
9 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40533928
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,
           yourtable.Status=tmpTable.Status,
           yourtable.[group]=tmpTable.[group]

            other fields here
0
 

Author Comment

by:rnhturner1
ID: 40533944
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.
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40533963
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.
0
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40533984
correction in the update query

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

Author Comment

by:rnhturner1
ID: 40534034
Pat
"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.
0
 
LVL 36

Accepted Solution

by:
PatHartman earned 500 total points
ID: 40534079
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.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40534207
;-)
0
 

Author Comment

by:rnhturner1
ID: 40544664
Rey,

Is there a way to just update all fields that are associated with that one APP ID?
0
 
LVL 36

Expert Comment

by:PatHartman
ID: 40544738
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.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question