|
|
|
COMPARE THE TWO TABLES |
|---|
| message from Rick on 23 May 2004 |
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
redburns@ameritech.net
|
| MacDermott replied to Rick on 24 May 2004 |
Welcome to the wonderful world of newsgroups.
As an Access beginner, I'm assuming that you're also a beginner here, and
may not realize that posting in all caps is considered shouting - something
to be avoided.
I think your problem can be addressed by using Access's "Unmatched Records"
query wizard.
From the database window, click Query - New, then select "Unmatched Records
Query".
The wizard should guide you through the rest.
HTH
- Turtle
"Rick" <anonymous@discussions.microsoft.com> wrote in message
news:10ff001c4412f$165fc520$a101280a@phx.gbl...
|
| =?Utf-8?B?U3RlZmFuaWU=?= replied to Rick on 4 Jun 2004 |
Hi Rick - For what you want to accomplish, the unmatched wizard query will not work. It can only unmatch one field. You will need to modify this. (I just figured this out myself!)
There is some prep work for your tables. Because you will be looking at all fields to see any unmatched, you may come across fields that are blank. In Access this is concidered "Null". And you cannot link a Null field to a Null field and get a match. For example, address book A may have no phone number as will address book B. Access will kick out this field as unmatched because they are Null. So once the prep work of fixing the null fields, take Table A and link ALL matching fields to Table B. Now I dont know how beginner you are, but you will want to show all records from Table A and only unmatched to Table B. So when you link the field, you will need to right click and choose the appropriate option to display all records from the "better" table. So you will need to determine whose address book is correct. And then there is a step to compare it back to the "incorrect" address. Let me try to outline this process for you. You will end up with 4 queries.
Qry 1 - Fix Null Fields for Table A. You will want to choose "make-table" query. Name it "Updated Address Book 1" or whatever floats your boat!. Bring every field into your query. You will want the Field box A to show -- Name: Nz([Field Name], "") Field box B to show -- Address: Nz([Field Address], "") -- and so on. "Name" will be the title of your column, "Nz" is changing your null value from "[Field Name]" and "" is changing the field to 0 (zero) string value. Do this for every field. This will only affect null values and will not change a different value already in the field.
Qry 2 - Follow step one on Table B. Name it "Updated Address Book 2".
Qry 3 - Make a table that will show all records in Table A that do not match Table B. This should be a Make-Table query as well. You can name it "Compare Address Table" Link your new tables by ALL fields that you want to compare or know that there could be veriances. For this example we will assume you want to see all records from Table A that dont match Table B. Right click every link and make sure that the link is Option 2 or 3. You want to show all records from only one table and only the records that match from the other. This depends on which way you dragged your link, left to right, or right to left. (tricky - you will actually be unmatching with the following steps.) Now drag all fields from table A into your query. And for all fields that you want to see the unmatched, drag those fields from Table B into your query. Uncheck the show box for all Table B fields. And then for each field from Table B, under criteria, Type "Is Null". And for every field, skip to the next line for the criteria. So that you are saying that Field A does not match, OR field B does not match, OR Field C does not match, etc. Hint - no criteria should be on the same criteria line.
Qry 4 - This will add all records in Table B that do not match to Table A. So you will see in the same table the good record and the bad record. Copy qry 3 and we will modify it. Change the action from a "make-table" to an "append table" you will append the same table. Add a second copy of Table B in your show tables. So you will have Table A, Table B, and Table B_1. Link ONLY the primary key field of Table A and Table B_1. Now instead of showing Table A fields in the query, show Table B_1. Leave the remiaing fields from the first Table B with all the critieria.
Now this will give you a list of the good records and the bad records according to Table A being the correct table. If you only want the bad records stop at qry 3. If you want to test both tables, run Qry 3 & 4 with the rolls reversed. Show all records in Table B to records in table A. And in Qry 4 - Change the copy of table B to the copy of table A.
If I have confused you more, I am happy to send you screen shots of what I did. I used this in our data system. I was able to identify variances in our Production Model to our Development Model in a matter of seconds. It is a very helpful tool. And once you are more experienced in Access you will probably find more tips and tricks that will suit you better.
|
|
