Microsoft Access Programmers North Carolina

Microsoft Access Programmer Information

Call MS Access Solutions at (323) 285-0939 For Complimentary Consultation

Using Action Queries

With action queries, you can easily modify data without writing code. In fact, using action queries is often a more efficient method than using code. Four types of action queries are available: update, delete, append, and make table. You use update queries to modify data in a table, delete queries to remove records from a table, append queries to add records to an existing table, and make table queries to create an entirely new table. Each type of query and its app.

Update Queries

You use update queries to modify all records or any records meeting specific criteria. You can use an update query to modify the data in one field or several fields (or even tables) at one time (for example, a query that increases the salary of everyone in California by 10%). As mentioned, using update queries is usually more efficient than performing the same task with Visual Basic for Applications (VBA) code, so update queries are considered a respectable way to modify table data.

To build an update query, follow these steps:

1. Click the Query Design tool in the Other group on the Create tab. The Show Table dialog box appears.

2. In the Show Table dialog box, select the tables or queries that will participate in the update query and click Add. Click Close when you're ready to continue.

3. Click Update in the Query Type group on the Design tab.

4. Add fields to the query that will either be used for criteria or be updated as a result of the query. In Figure 12.1, StateProvince has been added to the query grid because it will be used as a criterion for the update. DefaultRate has been included because it's the field that's being updated.

5. Add any further criteria, if you want. In Figure 12.1, the criterion for StateProvince has been set to CA.

6. Add the appropriate Update expression. In Figure 12.1, the DefaultRate is being increased by 10%.

7. Click Run on the ribbon. The message box shown in Figure 12.2 appears. Click Yes to continue. The Access Database Engine updates all records meeting the selected criteria.

You should name Access update queries with the prefix qupd. To adhere to standard naming conventions, you should give each type of action query a prefix indicating what type of query it is. Table 12.1 lists all the proper prefixes for action queries.

This material orginally appeared in Alison Balter's book Mastering Microsoft Office Access 2007 Development. Reprinted here by author's permission.

When you need a Microsoft Access programmer for your Anaheim CA business, call MS Access Solutions at (323) 285-0939. We have over 25 years experience in Microsoft Access programmer solutions. We create Access database applications for all sectors, consisting of hospitals, government agencies, the U.S. military, universities, agriculture, workers services, and insurance provider. We can take care of the most advanced as well as complicated Access and also SQL Server database programming for your business as well as smaller projects, like fixing damaged Access database forms, MS Access reports, Access macros, and VBA code.

Access programmer cities we serve: Access Programmers Charlotte North Carolina