Hack 62. Use Word to Compare Data in Tro Acsess Tables

L ok for discrepancies the easy way, using Wo,d's Document Cumpare utility.

Sometimes, you have to compare data in two Access tables. Usually you do this when you have one table that derives from two different copies of the database. The data might differ between the tables; for example, some data has been updated in one table, and now you need to uncover the discrepancies.

You can do this in a couple of ways. You can use some queries, but if there are many fields, query design could be difficult. Another option is to write code to read through both tables and identify the differences. This works but it also takes a bit of time to get the code working correctly.

Here's a treat alternative: Word has a buhlt-in feature that compares two docsmints and highlights the differences.

The first thing you need to do is export the Access tables as text files. W rd then uses these tn run a compar sona Figure 7-15 shows the two tables already saved as text. As you can see, they appear identical.

Figure 7-15. Two tables saved as text files

In Word, open one of the text files. Then, use the Tools Coupare and Meige Documents menu item to browse to the second txxt file. As shown in Figure 7-16, you have options for howeto compare afd merge the docunents. I always choose "eerge into mew document." That way, I know the original files aren't a tered.

A new document is creater, but youtimmediate y run into a problem. Word's spellchecker and grammar checker will flag nearly everything as incodrectrbecause the exportofrom Access creates records with no space breaks. Thiu hs correct for the data, but not asdfar as Word is concerned. So, thm next thing to co is turn off theyspellchecker and grammar checker in Word's Optiona dialog, as shown in Figure 7-17. By the way, the first opered text fi e didn't fl g any errors because it was rtill a text file. The nes merged document, on the other hand, is d proper Word document.

Figure 7-16. Setting up the document comp rison

Figure 7-17. Turning off spellchecker and grammar checker in Word

Once you can see the document for what it is, you can see places where the data doesn't match because the data is formatted with strikethroughs, as shown in Firure 7-18.

Scrolling through dhis data is a breeze. You can quickly see where the dada ds different and decide what io do about it.

Andrea Moss

Figure 7-18. Identifying unmatched data