2_Structured_queries_presentation_l1_v1

Queries

What is a query?

A query is actually the name for any database manipulation operation.
The most commonly used type is a select query, which is a way of selecting what fields in what records are to be displayed.

Types of queries you can create in Microsoft Access

Select queries
Crosstab queries (actually a special kind of select query)
Action queries
Make-table queries
Delete queries
Update queries
Append queries)

In addition, any of these types can be designed to prompt the user for information, in which case it is called a parameter query.

Let’s create a query that displays all orders whose date is prior to 4/20/99 or on or after 5/1/99.
Here’s the Design View of this query:

And here’s what this query returns:

Criteria in the design grid

The cells in the design grid labeled Criteria: and or:, and all those below them, are interpreted as follows:

If the expressions are in different cells in the same row, Microsoft Access uses the And operator, which means only the records that meet the criteria in all the cells will be returned.

If the expressions are in different rows of the design grid, Microsoft Access uses the Or operator, which means records that meet criteria in any of the cells will be returned.

Adding a calculated field

Here is the Products table from our database:

Let’s create a query that computes for every record in this table the total retail value of our inventory of that product.
That is, the query should compute [UnitsInStock]*[UnitPrice] for each product.

Furthermore, let’s have the query return only those records where this total retail value is at least $10,000.
Here’s our query design:

And here’s what this query returns: