Solved

Update Multiple Access Tables Using an Excel Spreadsheet for Update Values

Posted on 2008-06-26
8
902 Views
Last Modified: 2008-06-27
Please note my knowledge level is Beginner...
Is it possible to update multiple tables using an Access Update Query with linked tables and an Excel spreadsheet that contains the 'new' values?  

I have one Acesss table called 'Employee' which contains employee_id, employee_name, etc.  I have a second Access table called Address, which contains the employee_id, employee'_street, city, and state.  

I have an Excel spreadsheet that contains employee name and address changes.  Can I link Employee to Address on employee_id  and use the update query to update from the Excel spreadsheet all applicable name changes to the Employee table and applicable address changes to the Address table?  If this is possible, how would I do this?
0
Comment
Question by:rrtraverse
[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
  • 4
  • 3
8 Comments
 
LVL 44

Expert Comment

by:GRayL
ID: 21880449
Why do you have a second Access table called Address1  An address is a part of an Employee credential.  You need:

Employees
========
EmpID
FName
LName
Add1
Add2
City
State
Zip
Phone
etc.

If John and Martha Smith both work for you at the same address and phone number, you distinguish them by the EmpID - nothing else.

0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 21880503
Hello rrtraverse,

I'm with Ray.  The only reason you'd need a separate address table is if you wanted to be able to track
multiple addresses for a given employee (work address and home address, say) or if you wanted to
store the addresses on an effective-date basis.

Regards,

Patrick
0
 

Author Comment

by:rrtraverse
ID: 21883813
It was just an example to show what I was trying to do
0
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!

 
LVL 44

Expert Comment

by:GRayL
ID: 21884628
If you link to an Excel spreadsheet which contains both Name and Address data, you can use the linked table to replace/append the data in both your Employees and Addresses tables. Are we to assume the Excel table is a master list of names and addresses or does it contain only 'added' data?   That would dictate whether one used it in a SELECT INTO query or an INSERT query.



0
 

Author Comment

by:rrtraverse
ID: 21884802
The Excel spreadsheet will contain data changes
0
 
LVL 44

Accepted Solution

by:
GRayL earned 500 total points
ID: 21885498
OK, are you able to link to the Excel table from Access, using Files Get External Data?  If so, assuming the linked table is named ExData you need to create these two queries.

Insert ExData.EmpID, ExData.FName, ExData.DOB INTO Employees FROM ExData;

and

Insert ExData,EmpID, exData.Add1, ExData.Add2, ExData.City, ExData.State, ExData.ZIP INTO Addresses FROM ExData;

Open the query designer, click on Query Design, close the Show Table dialog box, right-click on a clear area of the pane, select SQL View, and paste the first string into the window.  Make changes to the names of the tables and fields as necessary, and then click on the big exclamation mark in the toolbar.  To satisfy yourself you are getting the correct data, Change Insert to Select, and remove INTO Employees.  This will change the query to a normal Select query - which is where I suppose you should have started.  Once you see you are getting the data you want, then change the Select to Insert and add INTO Employees after the list of fields.

Then do the same for the Addresses table.

0
 

Author Comment

by:rrtraverse
ID: 21886230
My question, which was obviously stated badly was whether or not you could update more than one table at a time.  Since your most recent response instructs me to update one table, and then the other, I will assume this means you can update only one table at a time.  

Thank You  
0
 
LVL 44

Expert Comment

by:GRayL
ID: 21886350
Thanks, but why the B?  If it were possible in a single query, of course I would have shown you that.  I saw 'Beginner' and went through considerable detail on creating and executing the queries.  Needless to say, I can hardly wait for the next question...
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

734 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