Using an append query, you can copy a selected set of information from one or more tables and insert it into another table. You can also use an append query to bring data from another source into your database-for example, a list of names and addresses purchased from a mailing list company-and then edit the data and insert it into an existing table. (You learned how to import data from external sources in Chapter 6, “Importing and Linking Data.”)
An append query, like a make-table query, provides a way to collect calculated totals or unnormalized rows from several tables. The difference is that a make-table query always creates a new table from the data you select, but an append query copies the data into an existing table that might or might not contain data. You must always modify the design of the table that a make-table query creates (if only to add efficient search indexes) to make it work optimally. Because the target table must already exist for an append query, you can explicitly define needed field properties and indexes (including a primary key) in advance. However, it’s easier to run into errors because you’re trying to insert data that’s already there (based on the primary key you defined), because the data you’re adding doesn’t match the data type you defined in the table, or because the new data fails one or more validation rules.
See “Troubleshooting Action Queries” on page 512 for a specific discussion of potential errors.
In the previous example, you saw how to take one of the complex queries you learned about in Chapter 8, and turn it into a make-table query. In truth, if you plan to collect such data over several months or years, you should probably design a table to hold the results and use append queries to periodically insert new historical data.
Another good use of append queries is to copy old transaction data to an archive table- either in the current database or another database. For example, after several months or years, the contact events table in the Conrad Systems Contacts application might contain thousands of rows. You might want to keep all events, but copying them to an archive table and deleting them from the main table can improve application performance. (You’ll learn about delete queries later in this chapter.)
Let’s build an append query to select old contact events and copy them to an archive table. Open the ContactsDataCopy.accdb database to follow along in this exercise. You’ll find an empty tblContactEventsHistory table defined in this database.
Start a new query with tblContactEvents. Because the ContactEventTypeID field is a “lookup” to tlkpContactEventTypes, it would be a good idea to preserve the original . description of the event rather than the code number. If you kept the original ContactEventTypeID numbers, any changes you made in the future to the related ContactEventTypeDescription information would also change the meaning in the archived records. So, add the tlkpContactEventTypes table so that you can store the current description rather than the ID in the history table. You should see a join line linking the ContactEventTypeID fields in the two tables.
Add the ContactID and ContactDateTime fields from tblContactEvents to the query grid. Include in the grid the ContactEventTypeDescription field from tlkpContactEventTypes. Finally, add to the query design grid the ContactNotes field from tblContactEvents. Because you are saving events in a history table, you don’t need the ContactFollowUp and ContactFollowUpDate fields from tblContactEvents.
You want to be able to filter the records on the date and time of the event. Each time you run this query, you probably don’t want to archive any recent events, so you need to create a prompt to select events that are a specified number of months old. A couple of handy date/time functions are available for you to do this: DateSerial and DateAdd. DateSerial returns a date value from a year, month, and day input. You can use it to calculate the first date of the current month like this:
DateSerial(Year(Date()), Month(Date()), 1)
Remember from Table 7–5 on page 376 that the Date function returns today’s date, the Year function returns the four-digit year value from a date value, and the Month function returns the month number from a date value. Supplying the value 1 for the day number gets you the date of the first day of the current month.
|Inside Out-Using Other Date Expressions|| |
You can usually think of more than one way to calculate a date value that you want To calculate the date of the first day of the current month, you could also write the expression
(Date() − Day(Date()) + 1)
This subtracts the day number of the current date from the current date-resulting in the last day of the previous month-and adds one. For example, August 17, 2007, minus 17 yields July 31, 2007, plus one yields August 1, 2007.
DateAdd adds or subtracts seconds, minutes, hours, days, months, or years from a date value. You specify the interval you want by choosing a value from Table 7–3 on page 366-in this case, you want m to indicate that you want to add or subtract months. Finally, you can include a parameter so that you can specify the number of months you want to subtract. So, under the ContactDateTime field, include the criterion
<DateAdd("m", −[MonthsAgo], DateSerial(Year(Date()),Month(Date()),1))
Click the Parameters button in the Show/Hide group of the Design tab below Query Tools and define the MonthsAgo parameter as Integer. The DateAdd function will return the date that is the number of specified months in the past from the first date of the current month. For example, if today is August 17, 2007, when you respond 6. to MonthsAgo, the expression returns February 1, 2007 (six months prior to August 1). Making sure that the value in ContactDateTime is less than this value ensures that you archive only contacts that are at least six months old. Your query up to this point should look like Figure 9–20.
Figure 9–20: This query finds old contact events to archive.
The sample database contains contact events from January 11, 2007, through July 9, 2007. If you want to experiment with this query, you need to take into account the current date on your computer. For example, if you’re running this query in January of 2008, you must specify a MonthsAgo value of no more than 12 to see any records. Likewise, specifying a MonthsAgo value of less than 6. shows you all the records.
Now, it’s time to turn this into an append query. Click the Append button in the Query Type group of the Design tab below Query Tools. You’ll see the dialog box shown in Figure 9–21, asking you where you want the selected rows inserted (appended).
Figure 9–21: After you click the Append button on the Ribbon, specify the target table of an append query.
Notice that the default is to append the data into a table in the current database. You can select the Another Database option and either type the path and name of the target database or click the Browse button to find the file you want. This feature could be particularly handy if you want to archive the records to another file. In this case, click the arrow to the right of Table Name, select tblContactEventsHistory in the current database, and click OK. Your query design now looks like Figure 9–22.
Figure 9–22: In the Append To row you can specify the target fields in an append query.
Notice that Access added an Append To line and automatically filled in the matching field names from the target table. Remember, you want to append the ContactEvent-TypeDescription field from tlkpContactEventTypes to the ContactEventType field in the history table, so select that field from the list on the Append To line under ContactEventTypeDescription.
You’re now ready to run this query in the next section. You can find the query saved as qxmplArchiveContactEvents in the sample database. You’ll also find a companion qxmplArchiveContactProductsquery.
As with other action queries, you can run an append query as a select query first to be sure that you’ll be copying the right rows. You can start out by building a select query, running it, and then converting it to an append query, or you can build the append query directly and then switch to Datasheet view from Design view to examine the data that the query will add. Although you can find and delete rows that you append in error, you can save time if you make a backup of the target table first.
After you confirm that the query will append the right rows, you can either run it directly from Design view or save it and run it from the Navigation Pane. When you run the qxmplArchiveContactEvents query and respond to the MonthsAgo prompt so that Access archives events earlier than March 1, 2007, Access should tell you that 34 rows will be appended, as shown in Figure 9–23. (For example, if the current date on your computer is any day in May 2007, enter 2, in response to the prompt for the MonthsAgo value.) If you want to append the rows to the tblContactEventsHistory table, click Yes in the confirmation dialog box. Note that after you click Yes, the only way to undo these changes is to go to the target table and either select and delete the rows manually or build a delete query to do it.
Figure 9–23: This dialog box asks you to confirm the appending of rows.
Go ahead and append these 34 rows. In “Troubleshooting Action Queries” on page 512, we’ll take a look at what happens if you try to run this query again.