A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

COMPARE THE TWO TABLES



 
 
Thread Tools Display Modes
  #1  
Old June 4th, 2004, 04:36 PM
Stefanie
external usenet poster
 
Posts: n/a
Default COMPARE THE TWO TABLES

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.
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 09:40 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.