Querying two different tables to find differences in matching records
Posted on 2008-10-30
I have an Employee Table (tbl_Employee) in my DB that I want to allow a "Power User" of this DB to maintain by importing an Excel spreadsheet from an old HR system to compare records. I have a macro set up to import the new records into a temporary table (tbl_Employee_Temp) with exact columns and names matching tbl_Employee including:
I have a query already done and working that identifies brand new records in the import file (tbl_Employee_Temp) that do not exist at all in tbl_Employee, so it adds these to tbl_Employee. But, I also want to be able to run a second query that identifies any record that may exist in both tables, but has some difference to it. For example, if an employee changes departments, I would have a record in tbl_Employee with the old HomeDept, and a record in tbl_Employee_Temp with the exact same Employee#, LastName, FirstName, and DOH, but a new HomeDept. How do I create a query to return just records like this?
Consider the Employee# field the binding field. This is the only field that would have to be exact all the time. Any of the other four columns may have differences and this would still be considered the same record as long as the Employee# matches on both.