Solved

Querying two different tables to find differences in matching records

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

Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

776 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