COMPARE THE TWO TABLES

  • Thread starter Thread starter Rick
  • Start date Start date
R

Rick

PLEASE HELP!!!

I HAVE TWO TABLES WITH THE SAME FIELDS, BUT TWO DIFFERENT
SOURCES. ONE IS MY BROTHER'S ADDRESS BOOK AND ONE IS MY
MOTHER'S BOTH TABLES HAVE OVER 10000 RECORDS IN THEM. THE
FIELDS ARE SOURCE, NAME, ADDRESS, CITY, STATE, ZIP AND
PHONE NUMBER

I WOULD LIKE TO COMPARE THE TWO TABLES BY LINKING THEM
TOGETHER AND LIST THE DIFFENCS IN RECORDS, IN THE ADDRESS
FIELD. EXAMPLE IF MY BROTHER HAD 600 WHITEHOUSE ROAD AND
THE OTHER ONE DID NOT LIST THAT AND IF THE OTHER TABLE HAD
10 UNITED NATION ROAD LIST THAT RECORD OUT

I DO KNOW I HAVE TO LINK THE TWO TABLES TOGETHER, BUT THAT
IS ALL I KNOW. CAN YOU HELP ME WITH SETING THIS DATABASE
UP AND THE CRITERIA?

A BEGININGER
(e-mail address removed)
 
Hi,



SELECT a.name, a.address, b.name, b.address
FROM a LEFT JOIN b ON a.name=b.name
WHERE Nz(a.address<>b.address, -1)

UNION

SELECT a.name, a.address, b.name, b.address
FROM a RIGHT JOIN b ON a.name=b.name
WHERE Nz(a.address<>b.address, -1)




Hoping it may help,
Vanderghast, Access MVP
 
Can you expand on your reply? It sounds like a good idea
but you have to remember that I am a beginner and all I
use is the wizard.
Rick
 
Hi,


Basically, I use the join as a lookup. If there is a match on the name, I
also look, in the where clause, if the addresses of the name-matched records
are the same. If there is no match, on the name, the unpreserved table (b
in the first case, a in the second case) supply NULL value for address.
Since there is no match, I assumed you also want to get that address
reported, so the NULL (result of the evaluation of the <> comparison in
the WHERE clause ) is transformed into a true (-1, in Jet) and the record is
kept, as desired.


The first query look for a not in b, the second query look for b not in a,
and both also consider the records having same name, but different address.
The UNION merge, vertically, the two results, but remove duplicated record
(else, UNION ALL would have been used).

You have to type UNION query, since the query designer does not support
them, graphically. You can "graphically" write each SELECT, individually,
in the designer, on the other hand.



Hoping it may help,
Vanderghast, Access MVP
 
Back
Top