You use queries to view, change, and analyze data in different ways. You can also use them as the source of records for forms and reports.

The most common type of query is a select query. A select query retrieves data from one or more tables using criteria you specify, and then displays it in the order you want.

You will create a query from scratch using the Design View in this lecture. In the Design View, you specify the data you want to work with by adding tables or queries that contain the data and then by filling in the design grid.

In the Database window, click the Queries tab, and then click New.

In the New Query dialog box, click Design View, and then click OK.

In the Show Table dialog box, click the tab that lists the objects whose data you want to work with.

Double-click the name of each object you want to add to the query, and then click Close.

If you have multiple tables or queries in the query, make sure they are connected to each other with a join line so that Microsoft Access knows how the information is related. If the tables or queries are joined, you can change the type of join to affect which records the query selects.

Add fields to the query by dragging the field names (alternatively use the double click method) from the field list to the design grid.

Refine your query by entering criteria, adding a sort order, creating calculated fields, computing the sum, average, count, or another type of total on the data it retrieves, or otherwise modifying the query's design.

To save the query, click Save button on the toolbar. Enter a name that follows Microsoft Access object-naming rules, and then click OK.

To see the results of the query, click View button the toolbar.

To delete a criteria row, click anywhere in the row, and then click Delete Rows on the Edit menu.

Open a query in Design view, or display the Advanced Filter/Sort window for a table, query, or form.

Click the first Criteria cell for the field you want to set criteria for.

Enter the criteria expression by typing it or by using the Expression Builder. To display the Expression Builder, right-click in the Criteria cell, and then click Build.

To enter another expression in the same field or in another field, move to the appropriate Criteria cell and enter the expression.

Text Fields:

Date/Time fields:

Null or 0-length string test

Using part of a field value

Domain aggregate functions

Using OR and AND

               Figure 1

Figure 1 matches to the query which displays the title, author and year attributes for those books that have been published in 1996 or 1997.

                Figure 2

Figure 2 corresponds to displaying the title, author and year fields for those records that were published in 1997 or that were written by Ozlem.

                Figure 3

Figure 3 matches to displaying the title, author and the year published for those books who were written by Ozlem and published in 2000.

Open a query in Design view or display the query.

To sort on more than one field, first arrange the fields in the design grid in the order you want the sorts performed. Microsoft Access sorts on the leftmost field first, then on the next field to the right, and so on. For example, to sort on the LastName field first and then on the FirstName field, the LastName field must be to the left of the FirstName field in the grid.

In the Sort cell for each of the fields you want to sort on, click an option.

To see the results of the query, click View button on the toolbar.

You perform calculations in a query using:

Predefined calculations, called "totals," to compute the following amounts for groups of records or for all the records combined in the query: sum, average, count, minimum, maximum, standard deviation, or variance.

Instead of displaying a calculation's results, you can use them:

As criteria to determine the records the query selects or to determine which records to perform an action on.

To update data from an update query.

You can create a new field that displays the results of a calculation you define with an expression.

Open the query in Design view.

Do one of the following:

Type an expression in an empty cell in the Field row. If the expression includes a field name, you must place brackets around the name.

If you need help creating the expression, use the Expression Builder. To display it, right-click in the Field cell where you're adding the calculated field, and then click Build.

After you press ENTER or move to another cell, Microsoft Access enters the default field name ExprN, where N is an integer incremented for each new expression field in the query. The name appears before the expression and is followed by a colon. In a datasheet, this name is the column heading.

You can select ExprN and type a more descriptive name, such as NewPrice.

If the expression includes one or more aggregate functions (Sum, Avg, Count, Min, Max, StDev, or Var), and if the design grid includes another field or fields that you want to use for grouping, click Totals (the sigma sign) on the toolbar (unless the Total row is already displayed). Leave Group By in the Total cell for the grouping field, and in the calculated field, change Group By to Expression.

If you want, do any of the following to complete the query:

Enter criteria to affect the calculation..

Sort the results

Set field properties such as Format (since the field doesn't inherit properties from the underlying table).

NOTE: To enter or view an entire expression without scrolling, press SHIFT+F2 to display the Zoom box.

Sample1:

AGE:(Date()-[BirthDate])/365

Sample2:

GPA:[QualityPoints]/[Credits]

This query will yield in the following table:

The corresponding design view is as follows:

The result of this query would look like:

The design of this query should be as follows:

The result of this query looks like:

The design of this query should be as follows:

The result of this query will be as follows:

The design of this query should be as follows:

AGE=INT( (DATE() – BirthDate) / 365 )

The result of this query is:

The design of this query is

The result of this query is as follows:

There may be several solutions to this query. Two that follows are: