Solved

Querying two different tables to find differences in matching records

Posted on 2008-10-30
4
329 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

Is Your DevOps Pipeline Leaking?

Is your CI/CD pipeline a hodge-podge of randomly connected tools? You’ve likely got a tool to fix one problem & then a different tool to fix another, resulting in a cluster of tools with overlapping functionality. Learn how to optimize your pipeline with Gartner's recommendations

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

688 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