2_Structured_queries_presentation_l3_v1

Queries

Numerical comparison operators

<
<=
>
>=

=
<>
Between

Less than
Less than or equal
Greater than
Greater than or equal
Equal
Not equal
Within the range (inclusive)

Arithmetic operators

^ (exponentiation)
* (multiplication)
/ (division)
\ (division that returns an integer result)
Mod (divide two numbers and return only the remainder)
+ (addition)
- (subtraction)

‘Like’ and ‘Is’ comparison operators

The Like operator is a pattern-matching operator used to compare two strings.
With Like, the single-character wild-card ‘?’ and the arbitrary-length wild-card ‘*’ can be used, as in ‘Like A*’.
The Is operator is most often used in the context Is Null, to test whether a field is blank. (Is Not Null is satisfied in just the opposite case.)

Logical operators

And


Or

Not

Used to perform a logical conjunction on two expressions.
Used to perform a logical disjunction on two expressions.
Used to perform a logical negation on an expression.

The ‘In’ operator

Determines whether the value of an expression is equal to any of several values in a specified list.

Parameter queries

A parameter query prompts for criteria each time it's run.
A parameter query displays one or more predefined dialog boxes that prompt you for the parameter value (criteria).
You can also create a custom dialog box that prompts for the query's parameters.

For example, suppose we often want to see certain data for all customers living in a particular city, where that city may be Miami, or Coral Gables, or any one of many other cities.
Rather than create a separate query with the city built in, it may make more sense to create a single parameter query that prompts for the city before it runs.

Creating a parameter query

In the Criteria cell for each field you want to use as a parameter, type a prompt enclosed in square brackets. Microsoft Access will display this prompt when the query is run. The text of the prompt must be different from the field name, although it can include the field name.

For example, a simple modification of our earlier query to list all customer data for Miami customers makes it a parameter query that prompts for the city:

For a field that displays dates, you can display the prompts "Type the beginning date:" and "Type the ending date:" to specify a range of values. In the field's Criteria cell, type Between [Type the beginning date:] And [Type the ending date:].

Crosstab queries

A crosstab query displays summarized values (sums, counts, and averages) from one field in a table and groups them by one set of facts listed down the left side of the datasheet and another set of facts listed across the top of the datasheet.

To create a crosstab query using a wizard

In the Database window, click the Queries tab, and then click New.
In the New Query dialog box, click Crosstab Query Wizard.
Click OK.
Follow the directions in the wizard dialog boxes. In the last dialog box, you can choose to run the query or see the query's structure in Design view.

Action queries

An action query is a query that makes changes to many records in just one operation.
There are four types of action queries:
delete,
update,
append, and
make-table.

Delete query

A delete query deletes a group of records from one or more tables.
For example, you could use a delete query to remove products that are discontinued or for which there are no orders.
With delete queries, you always delete entire records/rows, not just selected fields within records.

You can use a single delete query to delete records from a single table, from multiple tables in a one-to-one relationship, or from multiple tables in a one-to-many relationship, if cascading deletes are enabled (for example, all customers from Miami and all their orders).

Simplest case: Delete records from one table or tables in a one-to-one relationship

1 Create a new query that contains the tables from which you want to delete records.
2 In query Design view, click the arrow next to Query Type on the toolbar, and then click Delete Query.

3 For the tables you want to delete records from, drag the asterisk (*) from the field list to the query design grid.
From appears in the Delete cell under these fields.

4 To specify criteria for deleting records, drag to the design grid the fields on which you want to set criteria.
Where appears in the Delete cell under these fields.

5 In the Criteria cell for the fields that you have dragged to the grid, type the criteria.
6 To preview the records that will be deleted, click View on the toolbar. To return to query Design view, click View on the toolbar again. Make any changes you want in Design view.

7 Click Run on the toolbar to delete the records.

Note: To stop a query after you start it, press CTRL+BREAK.

Important considerations when using a query that deletes records

Once you delete records using a delete query, you can't undo the operation. Therefore, you should preview the data that the query selected for deletion before you run the query. You can do this by clicking View on the toolbar, and viewing the query in Datasheet view.

You should maintain backup copies of your data at all times.
If you delete the wrong records, you can retrieve them from your backup copies.

In some cases, running a delete query might delete records in related tables, even if they're not included in the query. This can happen when your query contains only the table that's on the "one" side of a one-to-many relationship, and you've enabled cascading deletes for that relationship.

In that case when you delete records from the "one" table, you'll also delete records from the "many" table.

Update query

An update query makes global changes to a group of records in one or more tables.
For example, you can raise prices by 10 percent for all products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.

Change records as a group using an update query

1 Create a query, selecting the tables or queries that include the records you want to update and the fields you want to use for setting criteria.
2 In query Design view, click the arrow next to Query Type on the toolbar, and then click Update Query.

3 Drag from the field list to the query design grid the fields you want to update or you want to specify criteria for.
4 In the Criteria cell, specify the criteria if necessary.

5 In the Update To cell for the fields you want to update, type the expression or value you want to use to change the fields.
6 To see a list of the records that will be updated, click View on the toolbar. This list won't show the new values. To return to query Design view, click View on the toolbar again. Make any changes you want in Design view.

7 Click Run on the toolbar to create the new table.

Note: As usual, to stop a query after you start it, press CTRL+BREAK.

Append query

An append query adds a group of records from one or more tables to the end of one or more tables.
For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information in, you'd like to append it to your Customers table.

Append queries are also helpful for:
Appending fields based on criteria. For example, you might want to append only the names and addresses of customers with outstanding orders.

Sometimes you want to append records when some of the fields in one table don't exist in the other table. For example, a Customers table has 11 fields. Suppose that you want to append records from another table that has fields that match 9 of the 11 fields in the Customers table. An append query will append the data in the matching fields and ignore the others.

Make-table query

A make-table query creates a new table from all or part of the data in one or more tables.
Make-table queries are helpful for:
Creating a table to export to other Microsoft Access databases.
For example, you might want to create a table that contains several fields from your Employees table, and then export that table to a database used by your personnel department.

Make-table queries are also useful for making a backup copy of a table and for
Creating a history table that contains old records.
For example, you could create a table that stores all your old orders before deleting them from your current Orders table.