PDA

View Full Version : COMPARE THE TWO TABLES


Rick
May 24th, 2004, 02:32 AM
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

Michel Walsh
May 25th, 2004, 01:07 PM
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


"Rick" > wrote in message
...
> 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
>
>
>

May 27th, 2004, 01:30 AM
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

>-----Original Message-----
>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
>
>
>"Rick" > wrote in
message
...
>> 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
>>
>>
>>
>
>
>.
>

Michel Walsh
May 27th, 2004, 11:22 AM
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



"Michel Walsh" <[email protected]> wrote in message
...
> 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
>
>
> "Rick" > wrote in message
> ...
> > 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
> >
> >
> >
>
>

Google