How do I run the following query?

[zhuanyi]

Say for example I have the following 2 database tables, the first one contains the old employee data, and has the fields shown below:
oldEmployeeID
FirstName
LastName
DateOfBirth
HiringDate
TerminationDate
and another one containing the new employee data with similar fields but instead of oldEmployeeID, it is showing the newEmployeeID.
During the conversion process, something were messed up and instead of putting in the original hiring date of the workers into the new employee database, the conversion date was put in, which, depending on the mood of HR ladies, could be any date, and at the same time, of course, new employee join the company, and we assume their hiring dates were entered correctly. On top of that, there are some employee who were terminated before the conversion took place but we still need to keep a record of that.
And I created a third table, say, emplyeeAll with similar fields to the employee data tables.
So here is what I need to do: if the firstName, lastName and DateOfBirth in the old employee data table and the new employee data table matches, I would assume they are the same employee, hence I would put the information for the employee obtained from the new employee data table to the employeeAll table, with the Hiring Date changed to the Hiring Date of the old employee data table (and do not copy the record from the old employee table to prevent duplicates), otherwise, I would simply copy and paste the data in new and old employee table to my employeeAll table.
I know this is really confusing, but...well...hope you know what I am saying...
So is it possible to do this by using Queries? Would Cross-Table query help? How should I build the query?
Or is it actually easier to do all these by writing out the SQL statement directly? If so, how should the statement looks like?

Thanks a lot!

Regards,
Anyi

 

[Michael J Ross]

I would use an update query to update the hire dates in your new employee data table.

Join the old employee table and new employee data table as you described by name and dob.

You could also use an append query to add records of those employees (the terminated ones) that exist in the old employee table and not in the new employee table.