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 that you no longer need. You can remove sets of records from your database using a delete 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 calculate automatically 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 in the ContactsDataCopy.accdb database 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
<[Oldest 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 11-24.
When you switch to Datasheet view for this query, Access prompts you for a date parameter, as shown in Figure 11-25. In the Enter Parameter Value dialog box, enter 9/1/2010 to see all the old contact events from September 1, 2010, or earlier. The result is shown in Figure 11-26.
Inside Out: Using Different Date Formats
Access 2010 recognizes several different formats for date parameters. For example, for the first day of September in 2010, you can enter any of the following:
|
|
|
The append query that you saw earlier that copied these rows to an archive table copied 23 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 11-27. Do not run this query! We’ll explain why in the next section.
Figure 11-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 criterion or more on the Criteria and Or lines.
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 the section Running an Update Query, 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 “all the rows” 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 11-25) again. Access selects the rows to be deleted and displays the confirmation dialog box shown in Figure 11-28.
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?)
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 the section Updating Groups of Rows, 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 11-29.
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.