Solved

Querying two different tables to find differences in matching records

Posted on 2008-10-30
4
324 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
Comment Utility
Please clarify the database type and version.
 
Thanks,
0
 
LVL 44

Accepted Solution

by:
GRayL earned 250 total points
Comment Utility
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
Comment Utility
It worked perfectly! Thanks for the help!
0
 

Author Comment

by:jmcclosk
Comment Utility
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

771 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now