Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Update Queries 3

Status
Not open for further replies.
metrodub

metrodub

Technical User
Dec 29, 2004
82
US
Not sure how to do this and was hoping for a little help/guidance.

I have an employee table in my database. Every month, I get an updated excel file populated with current employees. I am able to run an update query to update job titles, department names, etc, etc.

What I don't know how to do is this: Employee lists are generally dynamic with hirings, firings, or people moving on to other companies. What kind of query would I run so that if an employee in the new file (imported as a table) is not in the original table, that recordset is added to the original table. I would assume that I wouldn't want to delete employees from the original table that are no longer with the company so that any data that they are associated with isn't lost.

Thanks for your help.
 
Sort by date Sort by votes
It's usually a two-step process. First run an INSERT query to append new employees to the table and then run an UPDATE to modify information.

You're correct. Rarely do you want to delete such data because you do need to retain history for taxation and other purposes. Neither an INSERT nor an UPDATE will remove the data so you don't need to worry about that.
 
Upvote 0 Downvote
Addin new employees and update existing can be done with one update query. This assumes you have a common primary key in both tables.

For example, in the Northwind.mdb I created a new employee table "EmployeeNew" with the same structure. The following SQL will update the fields as well as add new employees from EmployeeNew to Employee.
Code: 
UPDATE Employees RIGHT JOIN EmployeesNew ON Employees.EmployeeID = EmployeesNew.EmployeeID 
SET Employees.EmployeeID = [EmployeesNew]![EmployeeID], 
Employees.LastName = [EmployeesNew]![LastName], 
Employees.FirstName = [EmployeesNew]![FirstName], 
Employees.Title = [EmployeesNew]![title], 
Employees.BirthDate = [EmployeesNew]![BirthDate], 
Employees.HireDate = [EmployeesNew]![HireDate], 
Employees.Address = [EmployeesNew]![Address];

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Upvote 0 Downvote
Thanks to both of you for the quick responses. The solution worked perfectly.

 
Upvote 0 Downvote
I am assuming the first table following the UPDATE command describes the table to be updated. In the above case: UPDATE Employees

Does the order of the SET command matter?
You wrote:
SET Employees.EmployeeID = [EmployeesNew]![EmployeeID];

Thanks.

Robert

 
Upvote 0 Downvote
Not sure what you mean by "the order". It shouldn't make any difference if you use
SET Employees.EmployeeID = [EmployeesNew]![EmployeeID],
Employees.LastName = [EmployeesNew]![LastName],
Employees.FirstName = [EmployeesNew]![FirstName],
or
SET Employees.FirstName = [EmployeesNew]![FirstName],
Employees.LastName = [EmployeesNew]![LastName],
Employees.EmployeeID = [EmployeesNew]![EmployeeID],


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Upvote 0 Downvote
dhookom, nice trik!

Thanks for the knowledge and have a star and a [medal]
 
Upvote 0 Downvote
How would you limit your update to update existing records only, and not add new records?

Thanks.

Robert
 
Upvote 0 Downvote
Replace the RIGHT JOIN by an INNER JOIN.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244
 
Upvote 0 Downvote
I got it.... a RIGHT Join would do the trick.

I'm curious however as to why you use the "!" to separate Table!Field in this query and not the "." as in Table.Field.

Robert
 
Upvote 0 Downvote
My left pinky finger needed more exercise than my right ring finger.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

combo
  • Question
SQL data types in CREATE/ALTER TABLE queries 1
Replies
2
Views
606
combo
combo
TheresAlwaysAWay
  • Locked
  • Question
Union query provides different reslt than when summing the combined queries individually 1
Replies
12
Views
873
TheresAlwaysAWay
TheresAlwaysAWay
TheresAlwaysAWay
  • Locked
  • Question
Can not get Access 2016 queries to accept form value input or TempVars! input 2
2
Replies
21
Views
955
TheresAlwaysAWay
TheresAlwaysAWay
BTilson
  • Locked
  • Question
Virtually Identical Select Queries - One Works, Other Doesn't?
Replies
2
Views
256
combo
combo
cimoli
  • Locked
  • Question
Lock in YES answer for 3 queries.
Replies
1
Views
167
cimoli
cimoli

Part and Inventory Search

Sponsor

Back
Top