Compare same fields in two queries

[atledreier]

Ok, I have a problem...

We are two different companies doing work on the same project, and
using different databases for the same data...

Recipe for disaster, and I've been telling management since day one,
but that's a different issue.

I've been left with the glorious task of syncronizing the databases.

I have my query that select all my data, and I have the imported table
that contain all their data. I need to compare the two, and only list
a result where the two are different. basically show me which fields
and records that I need to fix.

Any easy way to do this?

 

[John Spencer]

First of all you need some mechanism to alow you to identify which
records match up. Do you have that? IF so, what is it (one specific
field or multiple fields)?

Then you need to identify which records are in database A and Not in
Database B. What do you want to do if the records are in A and not in B?
Add them to A, delete them from B, or it depends?

Now you need to work out the opposite in B and Not in A. Again the same
questions.

Finally, you need to work out which records have values that are
different. If the values are different, what do you want to do? Replace
A value with B value or the opposite.

OBVIOUSLY, this is not a trivial task.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[John W. Vinson]

Ok, I have a problem...

We are two different companies doing work on the same project, and
using different databases for the same data...

Recipe for disaster, and I've been telling management since day one,
but that's a different issue.

You got THAT right.

I've been left with the glorious task of syncronizing the databases.

Sympathy and commiseration...

I have my query that select all my data, and I have the imported table
that contain all their data. I need to compare the two, and only list
a result where the two are different. basically show me which fields
and records that I need to fix.

Any easy way to do this?

Maybe, depending on the structure of the tables. How (based on data in the
tables) can you tell which record from DatabaseA belongs with which record in
DatabaseB? Autonumbers will certainly NOT suffice; is there any data stored in
the table that CAN be used to link them?

 

[atledreier]

You got THAT right.


Sympathy and commiseration...



Maybe, depending on the structure of the tables. How (based on data in the
tables) can you tell which record from DatabaseA belongs with which record in
DatabaseB? Autonumbers will certainly NOT suffice; is there any data stored in
the table that CAN be used to link them?

The 'Other' database is really an Excel file, based on my database, so
all fields will match. I have a common key, Tagnumber, that can be
used as an identifier.

The problem as you all see is that the other company change things
without telling us, so the two have become unsychronized. All I really
need is to get some form of highlight where the two differ, then take
appropriate action for each field.

It's about 6000 records and each is up to around 50 fields, so there's
alot of data, but hopefully not too much needs correction.

 

[John W. Vinson]

The 'Other' database is really an Excel file, based on my database, so
all fields will match. I have a common key, Tagnumber, that can be
used as an identifier.

The problem as you all see is that the other company change things
without telling us, so the two have become unsychronized. All I really
need is to get some form of highlight where the two differ, then take
appropriate action for each field.

It's about 6000 records and each is up to around 50 fields, so there's
alot of data, but hopefully not too much needs correction.

Create a (rather monstrous) Query joining the two tables by TagNumber:

SELECT A.*, B.*
FROM localtable AS A INNER JOIN exceltable AS B
ON A.Tagnumber = B.Tagnumber
WHERE A.field1 <> B.field1
OR A.field2 <> B.field2
OR A.field3 <> B.field3
<etc etc>
OR A.field50 <> B.field50

This will find cases where both tables contain a record for a tagnumber but
the data differs in one or more fields.

To find records which exist in one table but not the other, you'll need an
Unmatched query:

SELECT A.*
FROM localtable AS A
LEFT JOIN exceltable AS B
ON A.Tagnumber = B.Tagnumber
WHERE B.Tagnumber IS NULL;

will find cases where the excel table has no matching record; reverse the
logic to find cases where there is an "extra" record in the spreadsheet.

 

[atledreier]

Create a (rather monstrous) Query joining the two tables by TagNumber:

SELECT A.*, B.*
FROM localtable AS A INNER JOIN exceltable AS B
ON A.Tagnumber = B.Tagnumber
WHERE A.field1 <> B.field1
OR A.field2 <> B.field2
OR A.field3 <> B.field3
<etc etc>
OR A.field50 <> B.field50

This will find cases where both tables contain a record for a tagnumber but
the data differs in one or more fields.

To find records which exist in one table but not the other, you'll need an
Unmatched query:

SELECT A.*
FROM localtable AS A
LEFT JOIN exceltable AS B
ON A.Tagnumber = B.Tagnumber
WHERE B.Tagnumber IS NULL;

will find cases where the excel table has no matching record; reverse the
logic to find  cases where there is an "extra" record in the spreadsheet.

I was afraid that was the answer.... Ok, I'll get to work, thank
you!