Compare two tables, grouped by one column from each

[BjornOfNorway]

Greetings from Norway,

I need your expertice in figuring out the best way to solve the following scenario:

I have two tables: OLD-DATA and NEW-DATA.

OLD-DATA looks like this:

Business-ID	Department-ID	Employer-ID	Start-date	Work-hours	Position	End-date
999888777	444555666	11223345678	20120625	3750	112233
999888777	444555666	99887765432	20100101	3750	332211

NEW-DATA looks like this:

Business-ID	Department-ID	Employer-ID	Start-date	Work-hours	Position	End-date
999888777	444555666	11223345678	20120625	3750	112233
999888777	444555666	99887765432	19101212	0800	112233
999888777	333222111	55667789012	20143101	3750	123456

As you may notice, the structure is the same, but the content may not be. In my example above, the first row of OLD-DATA and NEW-DATA matches, the second row has the same Employer-ID but unmatching start-date, work-hours, and position, while the third row only exists in NEW DATA.

The sole purpose of this query is to reflect the data that does not match.

So here's what I'd like the query to do:

1. The columns of the two tables should be placed next to each other
2. The rows should be grouped by Employer-ID
3. The matching fields should be left blank, while the unmatching fields should be shown.

At the end, the query should look like this:

Employer-ID	OLDDATA-Business-ID	OLDDATA-Department-ID	OLDDATA-Start-date	OLDDATA-Work-hours	OLDDATA-Position	OLDDATA-End-Date	NEWDATA-Business-ID	NEWDATA-Department-ID	NEWDATA-Start-date	NEWDATA-Work-hours	NEWDATA-Position	NEWDATA-End-Date
11223345678
99887765432			20100101	3750	332211				19101212	0800	112233
55667789012							999888777	333222111	20143101	3750	123456

I would be the happiest guy south of the North Pole if any of you could guide me through the best way of solving the puzzle above!

[ItsMe]

I believe you will have to evaluate each column, one column at a time. Have you tried using the unmatched query wizard?

[BjornOfNorway]

Hi, ItsMe! Thanks for your response!

I did try the unmatched query wizard (that was my first approach). However, the unmatched query only shows the umatching rows, while I need to hide every field (in any given column and row) that matches the corresponding field from the NEWDATA table.

Let me give you an update on the progress so far:
I have placed the columns of the two tables next to each other and successfully grouped them by Employer-ID through three queries:
- Query1: NEWDATA.* RIGHTJOIN OLDDATA.* ON NEWDATA.EmployerID = OLDDATA.EmployerID
- Query2: NEWDATA.* LEFTJOIN OLDDATA.* ON NEWDATA.EmployerID = OLDDATA.EmployerID
- Query3: Query1 UNION Query2

Now, all I need is the ability to hide the matching fields from Query1 and Query2. Temporarily I've "cheated" by using Conditional Formatting in a form which displays the union query, and "hides" the matching fields by making the font the same color as the form background. However, this is a dirty way of doing it, and it causes problems when I try to export the data.

I'm sure there's a better way of not showing the matching fields, I just don't know about it. Any help would be greatly appreciated!

[ItsMe]

What is the end goal? Are you trying to synchronize data in a table that resides in your database with outside data?

Perhaps an unmatched query that creates a temp table. You can look at your temp table and then run some code to update the original table in your database. I am guessing you would have to run the unmatched query on each column, independently.

[BjornOfNorway]

Hi again ItsMe,

The end goal is to pick out the differences in each table, analyze them, change them (if neccessary) and eventually import the changes to an Oracle DB.

I've been fiddling with your idea of running the unmatched query on each column (one query per column), and then unifying the results in another query that groups by EmployerID. That seems to work, but is your method of creating a temporary table a better approach?

[ItsMe]

I do not normally recommend temp tables but they do come in handy sometimes. I think this may be one of those instances where a series of action queries could compare the data, one column at a time in order to create/update a temp table. If you do not have multiple users, you could use a single "Table Object" and simply delete the records after you are finished. If it is a multi user environment, I suggest creating a table using a unique name for the table in addition to other constraints to ensure simultaneous updates/analysis are not occurring to a given record.