Getting to Know Microsoft Access, Part 6: Advanced Queries and SQL

Special Purpose Queries

The first type of special purpose query we’ll look at is the parameter query. The parameter query is similar to the select query except that, where the select query is static, the parameter query prompts the user for selection criteria before running. The second type of special purpose query we’ll look at is the AutoLookup query, which fills in values for you in related tables, saving data entry time.

Parameter Queries

Parameter queries are especially useful for finding activities that fall within a certain time period. You can use multiple parameters in a query and Access will prompt the user for each one.

To create a parameter query just begin with a select query by clicking the New Query button, selecting Design View in the dialog box, and selecting your table(s) or query(ies) from which to select data, like so:

Then, instead of entering criteria in the Criteria cell as you would in a standard select query, just enter the parameter text enclosed in brackets ( [ ] ). This text will make up the prompt the user is given when the query is run, as in the following screenshot that asks the user for the Category Name:

Besides asking the user for a basic text answer, you could also prompt them for a specific time period using the syntax Between…And in the Criteria cell in brackets. For example:

Between [Enter start date] And [Enter end date]

Select any other fields you want to appear in the query results by dragging the fields from the field list and putting them in the query grid in the appropriate places.

The default data type for a parameter query is text, but you can change that, for example, to a number data type by choosing the Query | Parameters menu option while in Query design view. In the dialog box that appears, enter the parameter precisely as it appears in the Criteria cell and select the appropriate data type, such as the number data type.

AutoLookup Queries

AutoLookup queries are meant to save time. It works like this: you enter one value into a query and, based on a related table, the query fills in the remaining values for you in the table datasheet. AutoLookup uses two tables with a “one-to-many” relationship between them.

You create an AutoLookup query by adding two tables to the query design and then dragging the join field from the “many” side to the query grid. Add any other fields to the table you want, except the primary key field from the parent table. Once you’ve created this query it can be used as the basis for other database objects such as forms or reports. An AutoLookup query design looks similar to the following:

Action Queries

Action queries are used to perform data management operations on multiple tables simultaneously. The various types of action queries include: update, append, delete, and make-table queries.

Update queries make changes to fields in a group of records. Append queries add one or more records from one table to the end of another table. Delete queries delete a group of records from a table. Make-table queries create new tables from data in other tables.

Before creating any action queries, make copies of the tables you’ll be working with, just in case you accidentally erase something (or everything)!

Update Queries

To create an update query, begin with a new query design, just as you did when you made your first parameter query. Then, select your table(s) and/or query(ies) from which to base your new query from the dialog box, and choose the Query | Update Query menu option in design view. Drag the fields you want to update from the list of fields in the table to the query grid. Enter the expression you wish to use to limit the records you will update in the Criteria cell. Then enter the expressions you’ll use to update the records in the "Update To:" cells. Finally, save and run your query. When you create an insert query the design view looks similar to the following:

You’ll notice that the Queries tab shows your new query with a exclamation point attached to it, as a warning, to let you know the query is an action query and as such will produce changes to your data. All the action queries you create are shown in the Queries tab with an exclamation point.

Append Queries

Append queries are used to copy fields from one selected table to another table. To create an append query you first need to select the fields you want to copy, and then select the table to copy those fields to, making sure, of course, that the target field has corresponding fields. To create the append query, first start a new query in design view by choosing the table from which you want to take the data, and choose the Query | Append Query menu option. You’ll be prompted to enter a table name. Next, choose the table to which you want to append the data. Select the fields you want to append to the table by dragging and dropping them into the query design grid. Add criteria as you like. Save and run the query. When you create an append query the design view looks similar to the following:

Delete Queries

Delete queries are very dangerous: always make a backup of the data you plan to delete before you run a delete query! The delete query removes entire records, not just selected fields.

To create a delete query that deletes from only one table, start a new query, choose the table from which you wish to delete records, and choose the Query | Delete Query menu option. Drag the asterisk from the field list to the query grid; the Delete cell now shows "From." Next, drag the field that has the value indicating a record should be deleted into the query grid and enter the appropriate expression in the Criteria cell. Save and run the query to perform the operation. When you create a delete query the design view looks similar to the following:

To delete from multiple tables you will need to run multiple delete queries (assuming the relationship between the related tables doesn’t have the Cascade Delete Records option selected). First you’ll delete the records from the table on the “many” side followed by the records from the table on the “one” side. To do this, choose the Delete Query option as you did previously. Then drag the field to use for criteria to the grid and drag the asterisk from the field's lists of the tables on the “many” side to the query grid. Run the query. Next, remove the “many” side tables from the Query window and drag the asterisk from the “one” table to the query grid and run it again.

Make-Table Queries

Make-Table queries are useful for a variety of purposes including the following:

• Controlling exported information so that confidential data is not included in the new table.
  
  
• Using as a record source for a report of events occurring during a specific time period.
  
  
• Replacing records in an existing table with new records.
  
  
• Exporting information from related tables to other programs such as Excel.
  
  
• Exporting records to another database.

You create a Make-Table query by starting a new query, as before, choosing the tables or queries from which you want to take your records, and selecting the Query | Make-Table Query menu option. Next you enter a name for the table you are making. If you choose a table that already exists then any data in that table will be replaced by the new data. Drag the fields you want to put in the new table from the field lists and put them into the query grid. Run and save the query. When you create a make-table query the design view looks similar to the following:

SQL (Structured Query Language)

There are many fine resources for learning SQL online including this external site: http://www.sql.org/. The Microsoft Access help feature also provides all of the SQL commands you can use in Access, including the appropriate syntax and conventions.

SQL is the language relational databases use to program all queries. SQL is very easy to understand, for a programming language. However, it is too large to discuss in-depth in this tutorial. To view or edit SQL statements while you’re working on a query, at any time, you can simply choose the View | SQL View menu option, as in the following screenshot:

A simple SQL statement, using the select query type you learned about in the last lesson, might look something like this:

SELECT *
FROM [Customers]
WHERE [Last_Name]=”Adams”;

The above statement just says that you are using a select query, taking all the fields from the Customers table where the last name of the customer is Adams.

All SQL statements end with a semicolon. You’ll find out how to write the SQL code for all of the other queries talked about in this lesson using the Access help feature.

Next week we’ll start learning about forms and reports.