Numerous queries from simple to complex can be created. A few of the more popular queries include:
- Single-Table Select query
- Multiple-Table Select query
- Range query
- Complex query
- Totals query
- Action query
- Parameter query
- Crosstab query
The simplest query is a Single-Table select query. A single-table query is based on only one table. It is essentially the same as an advanced filter applied to a table. To create a query in Design View follow these steps:
- Add the data source(s). This can include table(s) and/or queries from the Show Table dialog window. For a single-table query, only one table will be selected.
- Join related data sources: Access does not automatically create joins between queries and tables that are not related. If Access does not create joins when data sources are added, joins should be created manually. Data sources that are not joined to any other data source can cause problems with the query results. To add a join, drag a field from one data source to a corresponding field on another data source. Access displays a line between the two fields to show that a join has been created. Note: the graphic below is NOT an example of a Single-Table query. A Single-Table query would only have one table, so a join would not be applicable.
- Add output fields – Double-click or add a field from a data source in the upper pane of the query design window down to the Field row of the design grid, in the bottom pane of the query design window. To quickly add all fields down to the Field row of the query design grid, double-click the table or query name from the upper pane to highlight all the fields in that source and then drag them all down to the design grid at the same time. Alternatively, double-click the asterisk in the table.
- Specify criteria – (Optional) Use the Criteria: and/or Or: rows in the grid to limit the records that the query returns based on whether data from the fields meet the specific criteria. Non-Select queries utilize this row extensively.
- Execute (Run) the query – To see the results of the query, click the Run button on the Query Tools Design tab. Access will display the query results in Datasheet view. To make additional changes to the query, click the View button to return the Query Design View to make necessary edits before re-running the query. If the query is complete, click the Save command in the Quick Access toolbar. When prompted to name it, type a descriptive name and click OK. The new query should now appear in the Navigation Pane in the Queries object group.
As previously stated, a relational database must have multiple tables. Therefore, Multiple-Table Select queries are far more common than Single-Table queries. The key to these queries is adding tables or queries that are related through joins via a common field. This may require a little planning before composing at the computer. Determine what information is needed for output, and then pinpoint which tables contain the fields needed to produce the information. Further analysis may reveal that criteria might be needed to streamline the results. The query in the previous graphic uses fields from four related tables.
A select query with criteria that matches a range of values is called a range query. This type of query relies on relational operators to restrict the query results. When a range query compares text, alphabetical order is used to determine which data matches the criteria. For example, to return all used vehicles less than $10,000 from a database of used vehicles, the criteria would be < 10000. Note the lack of a dollar symbol or comma delimiter? Adding a $ in front a number would change the datatype from a Number to a Text data type. Another example using the States table that tries to return results that do not include North Dakota results would use a criteria such as <> “ND”. Using the quotation marks indicates that the criteria is of the Text data type.
A complex query is any query that uses criteria in multiple fields. Criteria can include relational operators as well as logical operators. If criteria exists in the same row, the And operator is implied. The Or operator can be used in the Criteria row or using the Or: row below the Criteria: row.
Complex queries often utilize wildcard characters to add additional complexity. Complex queries sometimes include the same field twice to accommodate additional criteria. In these situations, the Show checkbox is often cleared to prevent the field from display twice in the query results. The graphic above illustrates a query that includes AND and OR criteria, as well as wildcard characters. The results should produce a list of music CDs that include any Classic Rock CDs and any Country CDs from Artists that include the name “Brooks” in any combination. This would include an Garth Brooks CDs as well as any Brooks and Dunn CDs.
There are times when setting simple criteria won’t generate the desired results , especially when working with numerical values. The objective may be to see the query results grouped or counted in some way. A Totals query uses a type of function called an aggregate function to sum the data in a query result set. Aggregate functions perform a calculation on a column of data and return a single value. Access provides a variety of aggregate functions, including Sum, Count, Avg (for computing averages), Min and Max. To sum data, add the Sum function to the query, to count data use the Count function, and so on. The following example will illustrate how many of various bakery menu items have been ordered. The query datasheet results at right display the quantity of various cakes ordered on separate order transactions.
Creating a Total query will group all identical menu items from separate orders, and then by using the Sum function, Access will add values in the Quantity field to calculate the total number ordered for each menu item. To create the Total query, the first step is to click the Totals button from the Show/Hide group of the Query Tools Design tab. This will add a Total: row to the query design grid.
The default values in the row are set to Group By. Clicking the drop-down arrow in the Quantity column of the Total: row, and selecting Sum will accumulate (add) the quantities of products ordered. Executing the query by clicking the Run button will display the results in the query’s datasheet view as shown above. Note the Quantity field name changes to SumOfQuantity.
In Access, queries can do a lot more than just displaying data. Through the use of assorted Action queries, Access can perform various actions on the database data. Microsoft Access provides 4 different types of Action Queries:
- Append – Retrieves data from one or more tables and adds the data to another table.
- Update – modifies or updates records. Criteria can be specified to limit which records are updated, but if no criteria is specified, all records will be updated.
- Delete – useful for quickly deleting a set of data on a regular basis.
- Make-table – retrieves data from one or more tables, and then loads the result set into a new table. That new table can reside in the database currently open, or a new database can be created.
Action queries change data based on some set of criteria. Action queries can delete records, update data, append data from one or more tables to another table, and make a new table. Users must be cautious because once the query is executed, the data is changed and cannot be undone. Experts advise to make a backup of the database before proceeding with significant action queries.
A parameter query retrieves information in an interactive manner by prompting the end user to supply criteria before the query is run. If a particular query is used often but modified slightly, a parameter query may be more efficient. When the query is run, the user is first prompted to enter a parameter (or multiple parameters). Once the parameter has been supplied, the query can return the results, based on the parameter provided. The key to creating parameter criteria is to enter the parameter prompt inside square brackets, such as: [What’s your favorite color?]. The square brackets will cause a parameter dialog box to open when the user runs the query. The text inside the square brackets will be displayed to the user, and an input field will be displayed for their input. If the user enters purple in the prompt, all records in table that store the data: purple will be returned as the query results.
In the example to the right, parameter criteria is entered in the Genre field’s Criteria cell.
When the query is executed, Access will display the following prompt: If the user were to enter the data: Pop in the prompt, the following results would be displayed the query datasheet view. (the data entered into the prompt is not case-sensitive)
A crosstab query is a special type of query that calculates a sum, average, or other aggregate function, and then groups the results by two sets of values — one down the left side (row headings) of the datasheet and the other across the top (column headings)- much like an Excel spreadsheet. When building a crosstab query in Design view, use the Total and Crosstab rows in the design grid to specify which field’s values will become column headings, which fields’ values will become row headings, and which field’s values to sum, average, count, or otherwise calculate.
In the following example, the Music database will calculate a Count on the number of CDs are possessed by Artist – using the Row Headings and by Genre – using the Column Headings. The Title field will serve as Value field. The data source is an existing query named Classic Rock CDs, which provides the fields necessary to create the cross-tab query. After executing the query the datasheet results appear similar to:
A crosstab query does not always populate all of the fields in the result set because the tables used in the query do not always contain values for every possible data point. It is possible to enter an expression in the Criteria row to limit the results for the column heading field. However, using a criteria expression with the column heading field does not limit the number of columns returned by the crosstab query. Instead, it limits which columns contain data. For example, suppose you have a column heading field that has three possible values: red, yellow, and blue. If you apply the criterion =’blue’ to the column heading field, the crosstab still displays a column for red and a column for yellow, but only the column for blue contains data.