Update Queries 3

[metrodub]

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.

 

[Golom]

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.

 

[dhookom]

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.

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]

 

[metrodub]

Thanks to both of you for the quick responses. The solution worked perfectly.

 

[rccline]

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

 

[dhookom]

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]

 

[JerryKlmns]

dhookom, nice trik!

Thanks for the knowledge and have a star and a

 

[rccline]

How would you limit your update to update existing records only, and not add new records?

Thanks.

Robert

 

[PHV]

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

 

[rccline]

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

 

[dhookom]

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]