Solved

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

Posted on 2015-01-06
9
149 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
  • 3
  • 3
  • 3
9 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
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 34

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
 
LVL 119

Expert Comment

by:Rey Obrero
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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 34

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 119

Expert Comment

by:Rey Obrero
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 34

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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

747 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now