Fundamentals of Access 2003

Creating and Using Queries

Understanding queries

Creating a query in design view

Saving the query

Saving query results

Exporting data results into an Excel spreadsheet

Understanding the query design grid

Adding additional tables to a query

Deleting a table from a query

Adding more fields to the design grid

Editing the query

Sorting a query

Querying for top values

Using criteria in queries

Telling Access what type of criteria you are using

Using operators with your criteria expression

Comparing values in one field with values in another field

Using multiple criteria

Finding records with empty fields

Building queries from multiple tables

Creating a query that fills in data automatically

Using the query wizard to create a summary query

Doing math in queries

Creating a calculated field

Making your calculated fields easier to see

Using operators in expressions

Using field names in expressions

Using functions in expressions

Functions

Using the Expression Builder

Understanding the Expression Builder

Getting help with functions

Working with dates and times in calculations

Using actual dates and times in expressions

Useful Date/Time functions

Working with text in expressions

Creating decision-making expressions

Creating flexible parameter queries

Creating more flexibility in parameter queries

Understanding queries

Queries are the object you build to ask questions of the data stored in your tables. They are frequently used as the basis for forms and reports. You can use queries to perform the following types of operations:

  • Look at data from related tables together
  • Look at subsets of data that meet certain criteria
  • Sort and alphabetize your data
  • Create new calculated fields

As with tables, queries have both a design view and a datasheet view. You build your query in design view, and then view the results in datasheet view.

Keep in mind that the results you see in datasheet view may change from viewing to viewing, depending on what has happened to the tables from which the data comes. For example, if you build a query to display lists of all vendors in a certain state, the results may change if a new batch of vendor information gets entered into the database.

There are seven types of queries:

  • Advanced Filter/Sort: allows you to find and sort data from a single table.
  • Select query: selects data from one or more tables and displays the data in the order you specify. This type of query can include criteria to filter certain records.
  • Totals or Summary query: a type of select query that allows you to calculate sums or other aggregates rather than displaying individual records.
  • Parameter query: a query that asks the user for one or more pieces of information to filter the data before displaying the results.
  • AutoLookup query: fills in information for you.
  • Action query: changes your data based on some set of criteria. Sample actions include deleting or updating data.
  • Crosstab query: creates tables with the values from one field down the side and values from another field across the top of the table and then performs a calculation on the data presented. Crosstab queries are similar to PivotTables in Excel.

We will be concentrating on the most-frequently-used type, the select query.

Creating a query in design view

Click on the Queries button in the Objects list on the left side of the database window.

Double-click the Create Query in Design View icon.


Query design view and the Show Table window open. Double-click on the name of the table(s) you want to use in your query to move them into the top pane of the query.

Notes:

  • There are separate tabs for tables and queries.
  • If you select multiple tables, a line appears between them to show any relationships that exist.
  • The field used as a primary key for the table appears in boldface.

Click on the Close button once you’ve selected all of the tables you would like to see.

The tables and queries you’re going to use to build your query display in the top of the window, and below them is the QBE (Query by Example) grid, where you construct your query.

Double-click on the first field you want to have in your query and it will pop into the grid below. You can also click on a field to select it and then drag it into the QBE grid.

Be sure to place the fields in the same order you want them to follow in the query.

When you’ve selected all of the fields you want to use, switch to datasheet view to see the results of your query.

Saving the query

Switch into design view and click on the Save button.


Give the query a name; remember to preface it with qry so that you can tell that this is a query when looking at lists of tables and queries. It’s also good to err on the side of longer, more descriptive names—query1 is not going to be a helpful name later on!

Saving query results

Remember that the results you see when you run a query will change depending on changes that are made to the underlying data. If it is important to save the results of a query so that you can see those results again, you can export the data to its own file.

Exporting data results into an Excel spreadsheet

Close the query and return to the main objects window.

Click on the File menu and select Export.

In the Export window, navigate to the location where you want to save the query results.

In the Save as type box, click on the dropdown arrow and select Microsoft Excel 97-2003.

Click on the Export button.

The results of your query are now stored in the location you specified as a static Excel file that has no relationship to the Access database.

Understanding the query design grid

All of the rows in the query design grid have their own purposes, which are outlined below:

Field: the name of the field used in the query

Table: the name of the table that the field comes from

Total: performs calculations in your query, but is not always visible. Click on the View menu and select Totals if it is not displaying.

Sort: determines sort order for your query.

Show: if the box is checked, the field displays in the results. If you need a field to create the query but don’t want it to display, then uncheck the box.

Criteria: the row where you specify the criteria or test the data must pass or match in order to display.

Or: if you need to enter multiple criteria, use this row.

Adding additional tables to a query

If you realize after starting the query that you need to add fields from another table,click on the Query menu and select Show Table, or right-click in the table pane of the query window and select Show Table from the shortcut menu.

Double-click on a table name to add it, or click on a table name and then click on the Add button.

Click on the Close button once you’ve added all the necessary tables or queries.

Deleting a table from a query

Click on the table to select it in the table pane of the query window.

Hit the Delete button on your keyboard.

Adding more fields to the design grid

You can add additional fields to a query using any of these methods:

  • Double-click on the field name in the table pane.
  • Drag the field name from the table pane to an empty column in the design grid.
  • Click into the Field box of an empty column and select the field name from the dropdown list.

Editing the query

Before you do any editing, remember to select the column by clicking into the column selector area at the top of the column. Once the column is highlighted, you can do any of the following:

  • Move a column: Click and drag the selected column to its new position.
  • Delete a column: Once the column is highlighted, hit the Delete key on your keyboard.
  • Delete all columns: Click on the Edit menu and select Clear Grid.
  • Insert a column: Drag a field from the table pane to the column in the design grid where you want it to appear. Access inserts a new column for the field and moves the existing columns to the right.
  • Change the displayed name of a field: At the beginning of the existing field name, type the new display name, followed by a colon. For example, My New Field Name: ClientID will display the label My New Field Name for that column when you switch into Datasheet view.

Sorting a query

The Sort row in the query design grid allows you to specify sorting order. You can sort by multiple fields, but remember that Access will always sort from left to right, meaning that it starts from the first column and works its way over.

Switch into design view.

Click into the Sort row for the first field by which you want to sort.

Click on the dropdown arrow and select Ascending or Descending.

Switch into datasheet view to see the results of your sort. If you don’t get what you were expecting, switch back into design view to revise the sort order.

Notes on sorting:

  • You cannot sort by a Memo or OLE Data field type.
  • If you sort by date, Access alphabetizes the months, which is probably not the result you want. The only solution to this issue is to put the query into report form, where for some reason Access knows to do a monthly sort in chronological order.

Querying for top values

The Top Values tool, which looks like the Zoom box, appears on the design view toolbar and allows you to see only the highest results for a query, which can be a very useful option for tables containing thousands of records.

You can specify a number for how many records you want to see, or a percentage of values.

Note: if you choose a percentage, keep in mind that you are asking for a percentage of the total number of records, not the records that fall into the specified percentage. For example, if there are two hundred client records and you ask to see the top 20%, you will see the 40 records that represent the top values, instead of those records that fall into the highest 20%, which might be more or less than 40.

Switch into design view.

Sort the data so that Access can tell what top values you want to see. For example, if you want to see the largest charges incurred by clients, you would sort the Service Charge field by Descending order so that the largest numbers appear at the top of the datasheet.

Click into the Top Values box and either select an option from the dropdown list or enter in your own value.

Switch into datasheet view to see the results of your query.

Using criteria in queries

Criteria allow you to limit the results displayed in a query. You can use dates, times, text, and values in criteria.

The most common way to enter criteria is to put what you are looking for into the criteria row of the design grid. In the screenshot below, the query will only return vendors with a state designation of VA:

Notice that once you click away from the Criteria field, Access automatically puts double quotes around the text to indicate that this is a text field.


If you wanted to look for vendors in another state, you can enter another abbreviation on the line below:

Note: Access does not pay attention to whether you enter your criteria using upper or lower case.

Telling Access what type of criteria you are using

As you have seen in the example above, Access automatically encloses what it assumes to be text in double quotation marks. If there’s a possibility of confusing Access, go ahead and insert the characters that define your element yourself:

Data type / Characters Used / Example
Text / Double quotation marks / “VA”
Date / Pound signs / #3-3-89#
Time / Pound signs / #12:00pm#
Number / 15
Field Name / Square brackets / [MyFieldName]

Using operators with your criteria expression

To make your criteria more complex, use some of the operators listed below.

Operator / What it does / Example / What you’ll see
= / Finds any value equal to what follows.
Finds values not equal to what follows. / >15 / Any records without 15 in this field
Finds values less than what follows / 15 / Any records with values less than 15 in this field
<= / Finds values less than or equal to what follows / <=15 / Any records with values less than or equal to 15 in this field
Finds values greater than what follows / 15 / Any records with values greater than 15 in this field
>= / Finds values greater than or equal to what follows / >=15 / Any records with values greater than or equal to 15 in this field
Between / Finds values between or equal to two values / Between 15 and 50 / Any records or equal to the range from 15 to 50
In / Finds values or text included in a list / IN(“VA”,”MD”,”WV”) / Any records with VA, MD, or WV in this field
Like / Finds matches to the pattern that follows / Like “V*” / Any records where the field begins with the letter V.

Comparing values in one field with values in another field

You can use criteria in combination with field names to filter based on the results of another field. For example, if you enter the criteria below:

>[TotalShipping]

Then your query will only display results where this field’s value is greater than the value in the TotalShipping field.

Using multiple criteria

If you want to use multiple criteria, be sure to pay attention to what line you put them on. Criteria on the same line have an AND relationship, meaning that the record must match all criteria in order to display.

Criteria on different rows are joined by OR. Records must match only one of the criteria in order to display.

The example shown below returns a list of Virginia vendors whose license expired between January 1, 2007 and January 1, 2009. The total number of vendors returned by this query is 25.



The example shown below is a much broader query, because it lists any vendor located in Virginia AND any vendor whose license expired between January 1, 2007 and January 1, 2009. The total number of vendors returned by this query is 59.

Note: to join criteria for a single field with AND, enter them into the Criteria line of the design grid with AND between them:

<2 AND >5

This criteria will return all records with a value less than 2 and values greater than 5.

Finding records with empty fields

If you want to find all records with no data listed for a particular field, use “Is Null” in the criteria field.

To find all records with something in the field, use “Is Not Null.”

Building queries from multiple tables

Once you build a query, it can be used as the basis of a form or report, which is how you are able to present data together even though it may be stored in separate tables.

In order to build a table that displays data from different fields, there must be a defined relationship between the two tables. Once the relationship is created, a black line joins the fields across tables.

To create the relationship within the query, click on the field in the “one” table (the table where that value is only listed once), and then drag over top of the field in the related table (the “many” table where that value may appear multiple times) so that it is highlighted.

Release the mouse, and a black line appears to show the linkage between the tables.

Note: if you want to use data from tables that are not directly related, you must display in the tables pane any other tables that relate the fields you want to display.

Note: if you create a query with fields from two tables that don’t have a relationship defined, Access doesn’t know what to do and ends up displaying every possible combination of records between the two tables. This kind of query is no good!

Creating a query that fills in data automatically

An AutoLookup query will allow you to enter one value and then have the query automatically show related data from the same table. For example, you can enter a client ID number and see all the appropriate contact information for that client.

The key to making an autolookup query work is that the query must include the linking field from the “many” side of a one-to-many relationship. This linking field is also known as the foreign key. The screen shot below shows how such a query would work using our sample database: