Deleting Groups of Rows

You’re not likely to keep all the data in your database forever. You’ll probably summarize some of your detailed information as time goes by and then delete the data you no longer need. You can remove sets of records from your database using a delete query.

Testing with a Select Query

After you have copied all the old contact event and contact product data to the archive tables, you might want to remove this information from the active tables. This is clearly the kind of query that you will want to save so that you can use it again and again. You can design the query to automatically calculate which records to delete based on the current system date and a month parameter as you did in the append queries.

As with an update query, it’s a good idea to test which rows will be affected by a delete query by first building a select query to isolate these records. Start a new query with tblContactEvents and include the asterisk (*) field in the query grid. A delete query acts on entire rows, so including the “all fields” indicator will ultimately tell the delete query from which table the rows should be deleted. Add the ContactDateTime field to the design grid, and clear the Show check box. This time, let’s use a specific date value to choose rows to delete. In the Criteria line under the ContactDateTime field enter

 <[0]dest Date to Keep:]

Click the Parameters button in the Show/Hide group of the Design tab, and define your parameter as a Date/Time data type. Your query should look like Figure 9–24.

Figure 9–24: This query uses a date parameter to select old contact events.

When you switch to Datasheet view for this query, Access prompts you for a date parameter, as shown in Figure 9–25. In the Enter Parameter Value dialog box, enter 3/1/2007 to see all the old contact events from March 1, 2007 or earlier. The result is shown in Figure 9–26.

Figure 9–25: Enter the query date parameter when Access prompts you.

Figure 9–26: When you run the select query, you can verify the rows to delete.

Inside Out-Using Different Date Formats 

Access 2007 recognizes several different formats for date parameters. For example, for the first day of March in 2007, you can enter any of the following:

 3/1/2007 March 1, 2007 1. MAR 2007

The append query you saw earlier that copied these rows to an archive table copied 34 rows, which matches what you see here. After you verify that this is what you want, go back to Design view and change the query to a delete query by clicking the Delete command in the Query Type group of the Design tab below Query Tools. Your query should look like Figure 9–27. Do not run this query! We’ll explain why in the next section.

Figure 9–27: Click the Delete button in the Query Type group on the Ribbon to convert your query to a delete query.

Notice that the query has a new Delete line. In any delete query, you should select From under the “choose all fields” (*) field for the one table from which you want to delete rows. All other fields should indicate Where and have one or more criteria on the Criteria and Or lines.

Using a Delete Query

Because you won’t be able to retrieve any deleted rows, it’s a good idea to first make a backup copy of your table, especially if this is the first time that you’ve run this delete query. Use the procedure described earlier in “Running an Update Query” on page 489 to make a copy of your table.

As you just learned, you can create a delete query from a select query by clicking the Delete command on the Design tab below Query Tools when your query is in Design view. You must be sure that at least one table includes the “choose all fields” indicator (*) and has From specified on the Delete line. Simply click Run in the Results group on the Design tab to delete the rows you specified. Because you included a parameter in this query, you’ll need to respond to the Enter Parameter Value dialog box (shown in Figure 9–25) again. Access selects the rows to be deleted and displays the confirmation dialog box shown in Figure 9–28.

Figure 9–28: This dialog box asks you to confirm the deletion of rows.

Are you really, really sure you want to delete these rows? Are you sure these rows are safely tucked away in the archive table? If so, click Yes to proceed with the deletion. Click No if you’re unsure about the rows that Access will delete. (We recommend that you click No for now and read on!) You can find this query saved as qxmplDeleteOldContactEventsUnsafe in the sample database. (Does the query name give you a clue?)

Deleting Inactive Data

You now know how to copy old contact event and contact product data to an archive table, and how to delete the old contact events from the main table. In some applications, you might want to delete more than just the event records. For example, in an order entry database, you might want to archive and delete the records of old customers who haven’t given you any business in more than two years.

In the Conrad Systems Contacts application, you can mark old contacts as inactive so that they disappear from the primary forms you use to edit the data. In “Updating Groups of Rows” on page 486, we showed you how to identify contacts who haven’t had any activity in a specified period of time and set the Inactive field so that they don’t show up anymore. Because of this feature, archiving and deleting old contacts isn’t an issue.

However, you might still want to delete old contact events and contact products that you have archived. We just showed you how to create a delete query to remove rows, but there’s a safer way to do it if you have copied the rows elsewhere. Go back to the query you have been building and add tblContactEventsHistory. Create a join line between the ContactID field in tblContactEvents and the ContactID field in tblContactEventsHistory. Create another join line between the ContactDateTime field in tblContactEvents and the same field in tblContactEventsHistory. Your query should now look like Figure 9–29.

Figure 9–29: This query allows you to safely delete archived rows.

Remember that the default for a join is to include rows only where the values in both tables match. Now your Delete query won’t return any rows from tblContactEvents (where you’re performing the delete) unless the row already exists in tblContactEventHistory! Run this query now and reply with any date you like. The query won’t delete rows from tblContactEvents unless a copy is safely saved in the archive table. You can find this query saved as qxmplDeleteOldContactEventsSafe in the sample database. There’s also a companion query, qxmplDeleteOldContactProductsSafe, to deal with contact products.