If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
COMPARE THE TWO TABLES
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 |
#2
|
|||
|
|||
COMPARE THE TWO TABLES
Hi,
SELECT a.name, a.address, b.name, b.address FROM a LEFT JOIN b ON a.name=b.name WHERE Nz(a.addressb.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.addressb.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 |
#3
|
|||
|
|||
COMPARE THE TWO TABLES
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.addressb.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.addressb.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 . |
#4
|
|||
|
|||
COMPARE THE TWO TABLES
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" vanderghast@VirusAreFunnierThanSpam 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.addressb.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.addressb.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 |
Thread Tools | |
Display Modes | |
|
|