Solved

Querying two different tables to find differences in matching records

Posted on 2008-10-30
4
327 Views
Last Modified: 2012-06-27
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:
Employee#
LastName
FirstName
DOH
HomeDept

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.
0
Comment
Question by:jmcclosk
[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
  • 2
4 Comments
 
LVL 4

Expert Comment

by:snowdog01
ID: 22842952
Please clarify the database type and version.
 
Thanks,
0
 
LVL 44

Accepted Solution

by:
GRayL earned 250 total points
ID: 22843058
SELECT a.LastName, b.LastName, a.FirstName, b.FirstName, a.DOH, b.DOH, a.HomeDept, a.HomeDept FROM tbl_Employee a INNER JOIN tbl_Employee_Temp b ON a.[Employee#] = b.[Employee#]   WHERE
a.LastName<>b.LastName OR
a.FirstName<>b.FirstName OR
 a.DOH<>b.DOH OR
a.HomeDept<>a.HomeDept;
0
 

Author Closing Comment

by:jmcclosk
ID: 31511758
It worked perfectly! Thanks for the help!
0
 

Author Comment

by:jmcclosk
ID: 22844113
Snowdog01, I wasn't sure what you were asking about.  If you were inquiring about what type of DB I was importing from, that was kind of moot because it is being dropped into an intermediate Excel Spreadsheet.  GRayL's suggestion worked like a charm, so I awarded him the solution.  Thanks!
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

726 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