Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Identify differences between two tables

Posted on 2014-12-09
17
Medium Priority
?
122 Views
Last Modified: 2014-12-09
I have two tables that both come from an Excel import.   Call them yesterday's import and today's import.   The two tables have exactly the same structure as far as field names are concerned.  But I need to find a way to identify the differences between the two tables as far a field values go.  Also I need to know if a record was deleted from yesterday's table or if a record was added to today's table.  Make sense?

How can I do this?

--Steve
0
Comment
Question by:SteveL13
[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
  • 8
  • 6
  • 3
17 Comments
 

Author Comment

by:SteveL13
ID: 40488672
I meant to also say that if a difference is found in an existing record, what was the change.  And also if a record was removed, which record was it.  And if a record was added, which record is it.

Probably I want to end up with a report that tells me all of this.

Possible?
0
 
LVL 18

Expert Comment

by:Simon
ID: 40488677
Something like this?

Select distinct * from
(
Select  * from yesterday
union all
select  * from today
) as subQuery
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488692
Here's the basic code:

SELECT *
FROM (SELECT ID, 
Sum(When) AS SumOfWhen
FROM (SELECT ID, 1 AS When
FROM Table1
UNION
SELECT ID, 2
FROM Table2)  AS Mytable
GROUP BY ID)
Where SumOfWhen <> 3

Open in new window

0
Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488697
The "SumOfWhen" will say:

1 - in the first table, but either different or deleted in the second table
2 - in the second table, but either different or deleted in the first table.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40488701
EE-28576965.accdb
This very simplistic database demonstates the principle.

Phillip's code is much better if you have unique IDs in each table and are interested in rows that are added or deleted. In it's present basic format it won't show changes to other columns in the table.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488716
In all of the lines which contain "ID", list all of the columns in your table. I haven't any additional data to work on.
0
 

Author Comment

by:SteveL13
ID: 40488722
Neither of the two tables have a primary key (ID) field.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488723
It will also list two rows, one with "1" and one with "2", if there are changes.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488731
It doesn't NEED an ID field - I don't know what fields you have, and I've got to put something.

So substitute the "ID" with the fields that you DO have.
0
 
LVL 18

Expert Comment

by:Simon
ID: 40488740
Apologies... I can see big flaws in what I hastily suggested, and I haven't time to improve on it at present.

@Steve, if you have no primary ID field, do you have fields that can be used as a composite (multi-field) unique key?
0
 

Author Comment

by:SteveL13
ID: 40488744
Phillip,

I'm sorry to be difficult but I don't know how to change the SQL you provided.  Here are my fields:

EmployeeID  --  Not a Primary Key field, is a text field
Employee  -  Text field
Date of Birth  -  Date field
Position  -  Text field
Status -- Text field


--Steve
0
 

Author Comment

by:SteveL13
ID: 40488745
Simon.  I suppose Employee and Date Of Birth.
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 2000 total points
ID: 40488757
No problem - SQL can be tricky:

SELECT *
FROM (SELECT EmployeeID, Employee, [Date of Birth], Position, [Status], 
Sum(When) AS SumOfWhen
FROM (SELECT EmployeeID, Employee, [Date of Birth], Position, [Status], 1 AS When
FROM Table1
UNION
SELECT EmployeeID, Employee, [Date of Birth], Position, [Status], 2
FROM Table2)  AS Mytable
GROUP BY EmployeeID, Employee, [Date of Birth], Position, [Status])
Where SumOfWhen <> 3

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488760
You will need to change Table1 and Table2 to the names of your tables (and probably add [ ]s around them)
0
 

Author Comment

by:SteveL13
ID: 40488812
Phillip,

This worked perfectly.  Now if only it could tell me:

1) Was a record added?
2) Was a record deleted?
3) If a field data changed.
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40488861
1. If there is a row with a "1" but not a "2", then a record has been added.
2. If there is a row with a "2" but not a "1", then a record has been deleted.
3. If there are two rows, one with a "1" and a "2", then a record has been changed.
0
 

Author Closing Comment

by:SteveL13
ID: 40489109
Absolutely perfect!!!  Thank you very much.
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

705 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