Chapter 10
In This Chapter
Fixing your mistakes
Replacing data automatically
Finding unwanted duplications in your data
Correcting an incorrect entry in an Access table is pretty easy: A few clicks, some typing, and voilà — the problem is gone. But what if you need to correct 26,281 records? Manually editing so many records would involve a whole bunch of clicking and typing and clicking and typing. (Editing an entire table by hand doesn’t sound like an opportunity for a triumphant “Voilà!”)
Fortunately, Access offers some handy, large-scale housekeeping and editing tools you can use to make big changes to your database — all without wearing out your keyboard, mouse, or fingertips.
Why do I sound so serious, all of the sudden? Well, when you’re making large-scale changes to a database, things can go wrong and mistakes can be made. If you’re going to do anything major to your database — especially if you’re editing and/or deleting a whole lot of records — you want a backup there behind you; if you do make a mistake and wipe out the wrong records, or edit something that should have been left alone, you can easily go back to the original version of the database and start over.
It’s a wise Access user who makes a backup of his or her database table or tables before starting any task that involves a margin for error. Here’s how you back up the table you want to edit:
Open the database file that contains the table you want to edit.
The list of tables in the database appears on the left side of the window.
Choose Copy from the pop-up menu that appears (see Figure 10-1).
Access places a copy of the table on the Windows Clipboard.
Right-click anywhere below the list of tables, reports, and so on in the left-side panel (as shown in Figure 10-2).
A pop-up menu appears.
Choose Paste from the pop-up menu.
The Paste Table As dialog box appears, as shown in Figure 10-3. It offers choices for how to paste your copied table data, but you don’t really need to worry about them at this point.
Type a name for the new table into the dialog box’s Table Name field.
Access suggests “Copy of _____,” where the blank is the current table name (which might be a good choice).
Click OK.
Don’t worry about the options in the dialog box. The default setting (Structure and Data) works just fine.
The dialog box closes, and you now have a copy of the original table.
With your table copy there to support you, you now have a backup — something to go back to should any of the steps in the rest of this chapter go awry when you apply them to your data.
Backup at the ready, it’s time to move on and start editing your database — automatically.
Automated editing queries have a lot of power. But before you haul out the really big guns, here’s a technique for small-scale editing. The technique may seem simplistic, but don’t be fooled; it’s really very handy.
You can practice small-scale editing by using the Replace command as follows:
On the Home tab, click the Find button in the Find section.
The Find and Replace dialog box appears, as shown in Figure 10-4.
In the Find What box, type the value you want to change. In the Replace With box, type a new value.
With this information in place, you’re ready to start making changes. Note that the Look In setting defaults to Current Field, which is why you clicked as instructed in Step 2.
(Optional) Click the Look In drop-down list to choose a different field in which to search.
The Find and Replace dialog box assumes you want to search and make changes to the active table (or form, report, or query, whatever’s the open and active part of your database). As mentioned previously, it defaults to searching the current field, but you can click the Look In drop list and choose Current Document instead, which means to search all fields in the active table, form, report, or query.
Click one of the buttons on the right side of the dialog box to apply the changes to your table.
The moment you click either the Replace or Replace All button, Access permanently changes the data in your table. Access lets you undo only the last change you made, so if you clicked Replace All and updated 12,528 records, Access lets you undo only the very last record that you changed — the other 12,527 records stay in their new form. This, dear reader, is why we started out making a backup!
When you finish, click Cancel or the X button in the top right corner of the dialog box.
The Find and Replace dialog box closes.
Click the Replace All button.
Your computer goes off and does your bidding, changing each instance of the word in the Find What box to the word in the Replace With box.
Access gives you a lot of control over the process. In addition to the options of the Find command (discussed in Chapter 12), Replace offers additional options:
Queries, especially those created through the Query Wizard, are exceptionally easy to create (as you discover in Chapter 13). In this chapter, you create a very simple and specific query not created elsewhere in the book — a query designed specifically to look for duplicate records.
But what about correcting those 26,281 records? If Find and Replace doesn’t solve the problem, you’re looking at some serious querying (a topic covered in Chapters 12 through 17), and you may find that creating a single query that fixes all the problems is impossible.
Therefore, be prepared to use a combination of things: Create a query that seeks out certain issues, do some editing of individual records, and use Find and Replace procedures to locate consistent errors and replace them with something you can spot easily, like every place someone doing data entry placed an “X” to indicate they didn’t know what to enter or didn’t have the data to enter for a given field. This is kind of a twist on using Find and Replace to fix spelling errors (covered previously in this chapter), but instead of fixing an error, you’re using Replace to flag certain records for editing.
Generally, however, Find and Replace will do what you need because a universal misspelling, a bunch of zip codes entered accidentally into the wrong numeric field — or any repeated error of that sort — can be fixed pretty quickly by searching for the erroneous content and replacing it with what should be there instead. With a backup copy of your table preserved for safekeeping (you did read the “Please Read This First!” section, didn’t you?), feel free to experiment with Find and Replace and even some queries that you make on your own.
Let’s get back to that duplicate records query. What, exactly, is a duplicate record? You will have duplicated data in your database — people who live in the same city, for example, will have the same city in their records in the City
field. Products that have the same price, or that come in the same colors, will have the same data in some or nearly all of their fields. What I mean here by a duplicate record is an entire record that is an exact duplicate — every field is the same in one record as it is in another record (or in several others, as the case may be).
How do duplicate records get made? It can happen quite easily:
The Find Duplicates Query Wizard can help you spot that common curse of the database: duplicate records. Duplicates waste time and money, especially when the database is used for mailings or some similar business activities. This wizard can help your database clean up its act.
To run the Find Duplicates Query Wizard, follow these simple steps:
Click the Ribbon’s Create tab.
The Create tab’s sections — Templates, Tables, Queries, Forms, Reports, and Macros & Code — appear.
Click the Query Wizard button, found in the Queries section.
The New Query dialog box opens, as shown in Figure 10-5.
Choose the Find Duplicates Query Wizard from the list of available wizards.
A description of the wizard’s function appears on the left side of the dialog box.
Click OK.
The original Query Wizard dialog box closes and is replaced by the Find Duplicates Query Wizard dialog box, shown in Figure 10-6.
Choose the table in which you want to search for duplicates.
You’ll also see your backup copy of the table in this list, so be sure you pick the right table and don’t start operating on your backup!
Click Next.
In the Available Fields list (see Figure 10-7), double-click those fields you’re worried could have duplicate entries in them. Skip fields that are supposed to have duplicates anyway or where duplicates, however unlikely, are no problem — such as cities, states, zip codes, or last names. You can also skip your primary key field, because by its very nature (as a field containing unique data for each record), it won’t have any duplicate values.
When the Duplicate-Value Fields list is populated with those fields that you want the query to look in, click Next.
The next step in the wizard appears, as shown in Figure 10-8.
From the Available Fields list, double-click those fields you want to include in the query’s results in addition to those with duplicate values.
Such fields typically include those that help you identify records that are harboring duplicate data, such as First Name if you’re looking for records that might have identical Last Names, or Product Numbers if you’re looking for products that have the same description or price. In this example, I am looking for duplicate records in a customer table, which will prevent mailings going out to anyone more than once (or at an old and a new address for the same person).
Click Next.
The last step in the Find Duplicates Query Wizard appears, as shown in Figure 10-9. Here’s where you decide on a name for your new query.
If you don’t like the default name Access gave your query, just enter a new name into the text field.
Preferably, the name should be short but should identify the query’s purpose — Duplicate Names Query is a good name for this example. The default query name that Access offers will be “Find duplicates for _______,” where the blank is the name of the table searched-for duplicates.
Click Finish.
The results of your query appear onscreen, as shown in Figure 10-10.
Now that you know which records have duplicate data within them, you can
Change that word to something else
or
You’ll be asked to confirm your deletion, and you can choose Yes if you do want to get rid of the extra record.