Action Queries

Action queries can filter and sort like select queries, but they do more than select queries. They actually change data in tables, by adding new records (append query), changing data in existing records (update query), or deleting records from a table (delete query). You can even create a new table from data in another table, or several other tables, using a make-table query.

Append Queries

Append queries are used to append (add) data from one table or query to another table. As an example of a typical use of an append query, let’s say that I obtained a list of companies in Excel worksheet format, and I wanted to add them to the tblMailingListCompanies table in the Toy Workshop sample database. After creating a linked table to let me work with the Excel data (see Chapter 1, Creating an Application, for information on creating linked tables). The linked table that displays the Excel worksheet data is called txlsMailingList.

The tag txls indicates a table linked to an Excel worksheet.

The target table (the table to which data is appended) is tblMailingListCompanies. To create an append query to append data from txlsMailingList to tblMailingList Companies, start by selecting the source table (txlsMailingList) in the database window, and selecting Query from the New Object selector, as shown in Figure 4.22.

Figure 4.22

Select Design View in the New Query dialog. Since you can only append data from the source table to fields that exist in the target table, for a handy reference, open tblMailingListCompanies in Design view, and size its window and the query designer window so you can see them both. We’ll need only the fields in txlsMailingList that match fields in tblMailingListCompanies; Figure 4.23 shows the selected fields.

Figure 4.23

At this point, the new query is still a select query; to change it to an append query, drop down the Query Type selector and select Append Query, as shown in Figure 4.24.

Figure 4.24

The Append dialog opens; select tblMailingListCompanies from the Table Name drop-down list, as shown in Figure 4.25.

Figure 4.25

After selecting the target table, Access tries to match up the source and target fields. Generally, it misses a few, because the fields have different names in the source and target tables. In this case, CompanyName, City, and PostalCode were matched, and the other fields weren’t. To manually match a field, select it from the drop-down list in the Append To row under the field, as shown in Figure 4.26.

Figure 4.26

Save the query with the tag qapp to indicate that it is an append query. Figure 4.27 shows the query with all the fields matched up.

Figure 4.27

To run the append query, first close tblMailingListCompanies, then click the Run button on the toolbar (it’s the one with the big red exclamation point). You’ll get a confirmation message telling you how many records will be appended. After you click the Yes button, if you don’t get any further messages; all the records were appended to the target table. If there are problems with appending, you will get an error message informing you of the error, and you can inspect the source table and either delete or manually correct the problem records.

Update Queries

An update query modifies data in one or more fields of a table. You can use an update query to replace a field value with another hard-coded value (say, replace all 914 area codes with 845, or replace a contact phone number or email address with a new one) or update a numeric field with the results of a calculation (add 10 percent to all prices) or replace a value in a field with a value from a field in another table. I’ll give examples of several types of update queries.

Unless you are storing areas codes in a separate field, to change an area code you need to replace just the first three characters of a phone number (if you don’t surround the area code with parentheses) or the second through fourth characters (if you do surround the area code with parentheses). An update expression for the first case can be done using the Left function, and one for the second case can be done with the Mid function. To create an update query, select the table to be updated in the database window, select Query from the New Object selector, then select Design View in the New Query dialog. I’ll select tblEmployees, which has two phone number fields that need area code updating.

After selecting Query from the New Object selector, it becomes the default object (for the time being), so to create another query, you can just click the New Object button (it will display the Query icon, as shown in Figure 4.28.)

Figure 4.28

Drag the field(s) to be updated to the query designer grid. An update query might update all records in a table, but more typically you just need to update certain records. In the example, the query only needs to update phone numbers that have the 914 area code. To select just these records, both the WorkPhone and HomePhone field need a criterion that looks for 914 in positions 2 through 4. The following expression on the WorkPhone field will select the appropriate records for that field, assuming that the phone numbers are formatted with dashes:

Mid([WorkPhone],2,3)=”914”

Save the query as qupdEmployeePhones (qupd is the LNC tag for an update query). It’s still a select query—this allows switching to Datasheet view to check whether the query is selecting the right records. To check whether an update query’s filter criteria are working correctly, switch to Datasheet view. Sometimes it’s helpful to make a calculated field using the expression you intend to use for an update expression, to see if it works before you try to use it as an update expression. I made a calculated field using the expression AreaCode: Mid([WorkPhone],2,3) and switched to Datasheet view to see what I got (the results are shown in Figure 4.29).

Figure 4.29

The results reveal a very common problem: Some of the phone numbers have parentheses, and some don’t, so the area code isn’t extracted correctly for all records. If you put an input mask on a phone number field to ensure consistent data entry, this won’t happen in the future, but applying an input mask won’t correct existing data. Before updating the area codes, it’s a good idea to make the phone number formatting consistent. This can be done with another set of filter criteria and update expressions.

In this case, the phone number format that uses dashes is correct, so we need to change the phone numbers that use parentheses. If you place the Like “(*” criterion on the WorkPhone field, the query will select just the records that need to be changed. Figure 4.30 shows the select query that filters for just the work phone numbers with parentheses, and Figure 4.31 shows the Datasheet view of the same query.

Figure 4.30

Figure 4.31

Now we need to create an update expression to convert the work phone numbers from the parentheses format to the dashed format. It’s helpful to create another test expression before changing back to an update query. The following expression does the conversion correctly for the WorkPhone field:

NewWorkPhone: Mid([WorkPhone],2,3) & “-” & Mid([WorkPhone],7)

Figure 4.32 shows the query in Design view with the test expression based on the WorkPhone field, and Figure 4.33 shows the same query in Datasheet view.

Figure 4.32

Figure 4.33

The query can now be converted to an update query, by selecting Update Query from the Query Type selector. Place the test expression above in the Update To row of the WorkPhone field (minus the label), with the Like “(*” criteria in the Criteria row (see Figure 4.34).

Figure 4.34

Run the update query by clicking the Run button on the Query Design toolbar, then modify the update query to update the HomePhone field, with a similar expression, and run it. (You can’t just place filter criteria on both fields and update both phone number fields in one pass, because then the updating will be done on all the records that have either a home or work phone that needs reformatting. This could result in some phone numbers being reformatted incorrectly, if the original numbers are formatted differently.) Figure 4.35 shows tblEmployees with all the phone numbers formatted with dashes.

Figure 4.35

Now that all the phone numbers are formatted the same way, we’re ready to do the area code updating for the WorkPhone field, using Like “914*” to select the records that need updating, and “845” & Mid([WorkPhone],4) as the update expression, as shown in Figure 4.36.

Figure 4.36

After running the modified update query, then modifying the query to update the HomePhone field similarly and running it again, all the phone numbers that had area code 914 now have area code 845, as shown in Figure 4.37.

Figure 4.37

Make-Table Queries

When you need a table, and not a query, as a data source, a make-table query can be useful. You may need a table to use for exporting to another application, or to provide an updatable form record source in place of a nonupdatable query. As an example, I’ll use a make-table query to create a table that can be imported into a flat-file mainframe database that has the entire employee name in a single field, using expressions from qryConcatenateNameComponents and qryConcatenateAddressComponents in the Query Expressions sample database.

Start by creating a query based on tblContactsSeparate, with ContactID from the table, and copy into the query grid the LastNameFirst concatenated field from qryConcatenateNameComponents. Since the target table needs to have the street address information in one field, and city, state, and zip in another field, copy the Address and CityStateZip concatenated fields from qryConcatenateAddressComponents into the new query’s grid. (Just copy the expressions from the other queries, as opposed to adding the queries to the new query’s pane.)

Convert the query into a make-table query by selecting Make-Table Query from the Query Type selector, and enter a name for the target table in the Make Table dialog. I use the tag tmak for tables created by make-table queries, so I can match them up with the queries that create them. Save the query with the tag qmak and the same base name (the query qmakContactsConcatenated creates the table tmakContactsConcatenated). Run the query by clicking the Run button on the toolbar; the table is created, after you click Yes on a confirmation message. tmakContactsConcatenated is shown in Datasheet view in Figure 4.38.

Figure 4.38

Delete Queries

A delete query deletes all the records in a table that match criteria you’ve specified. If you don’t specify any criteria, all records will be deleted from the table. If you need to delete all records from a table in VBA code, there is little point in creating a query for that purpose, since it only takes a one-line SQL statement to delete all records from a table. The following SQL statement deletes all records from the table referenced by the strTable variable; it can be run using the RunSQL statement:

“DELETE * FROM “ & strTable

The procedure that follows (from frmImportFromExcel in the sample Excel Data Exchange database for Chapter 13, Working with Excel) clears all records from tblCustomers, before filling it with new data imported from Excel.

 Private Sub cmdImportDatafromWorksheet_Click() On Error GoTo ErrorHandler        strWorkbook = GetDocsDir & "Customers.xls"    strTable = "tblCustomers"        ‘Clear old data from table    strSQL = "DELETE * FROM " & strTable    DoCmd.SetWarnings False    DoCmd.RunSQL strSQL        ‘Import data from workbook into table, using the TransferSpreadsheet method    DoCmd.TransferSpreadsheet transfertype:=acImport,        spreadsheettype:=acSpreadsheetTypeExcel9,        tablename:=strTable,        FileName:=strWorkbook,        hasfieldnames:=True        Me![subCustomers].SourceObject = "fsubCustomers"     ErrorHandlerExit:    Exit Sub ErrorHandler:    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description    Resume ErrorHandlerExit     End Sub 

If you need to clear just certain records from a table of imported data, a delete query is useful. After importing customer data from Excel, if you only need data for customers in the United States, you can run a delete query to delete all other records. To start, create a select query based on tblCustomers, and drag the asterisk at the top of the field list to the query grid, then drag the Country field to the grid. Uncheck the Country field’s Show checkbox because this field is only used for filtering. Enter the expression Not Like “USA” as the criteria for the Country field, and convert the query to a delete query by selecting Delete Query in the Query Type selector. Save the query with the qdel tag. If desired, switch to Datasheet view to check that only customers outside the United States are selected. Figure 4.39 shows the delete query.

Figure 4.39

Running this query will delete all the records in tblCustomers except those in the United States—at least if data entry of country names is consistent.