querying a database

SESSION 3.1

This section covers using the query window in design view to create a query and sorting & filtering data while in a datasheet view.

Microsoft Access 2000 Class #4

The Major Steps of a MicroSoft Access Database

Tables Queries Forms Macros Reports Modules

On our road map, we are here!On our road map, we are here!

A query is a question you ask about data stored in a database.

For example, you might create a query to find records in the Customer table for only those customers in a specific state.

When you create a query, you tell Access which fields you need and what criteria Access should use to select the records.

Customer ID

Name

Address

City State ZipAll Customers in Ohio

Access provides powerful query capabilities that allow you to:

Display selected fields and records from a table.

Sort records

Perform calculations

Generate data for forms, reports and other queries

Update, (modify) data in tables in a database

Find and display data from two or more tables

Most questions about data are generalized queries in which you specify the fields and records you want Access to select.

These common requests for information, such as “Which customers have unpaid bills?”or “Which type of coffeesells best in Ohio?” are called select queries.

The answer to a select query is returned in the form of a datasheet.

More specialized, technical queries, such as finding duplicate records in a table, are best formulated through a Query Wizard.

Cool

A Query Wizard prompts you for information through a set of questions and then creates the appropriate query based on your answers.

For common, informational queries, it is easier for you to design your own query rather than use a Query Wizard.

The Query Window

You use the Query window in Design view to create a query. In Design view you specify the data you want to view by constructing a query by example. Using query by example (QBE), you give Access an example of the information you are requesting. Access then retrieves the information that precisely matches your example.

In Design view, the Select Query window contains the standard title bar, toolbar, and status bar.

On the tool bar, the Query Type button shows a select query; the icon on this button changes according to the type of query you are creating.

The title bar on the Select Query window displays the query type, Select Query, and the default query name, Query1.

Field List

Design Grid

Tool Bar

View Button for Datasheet View

This button will toggle views between the datasheet view and the design view

The pull down arrow to the right of the button will show the options.

An alternative method of toggling between the design view and data sheet view is to use View on the Menu Bar.

Query Type Button

This button will toggle or change the type of query that we want.

The pull down arrow to the right of the button will show the options.

We will be using the Select Query Type for this class.

In the design grid , you include the fields and record selection criteria for the information you want to see. Each column in the design grid contains specifications about a field you will use in the query. You can choose a single field for your query by dragging its name from the field list to the design grid in the lower portion of the window. Alternatively, you can double click a field name to place it in the next available column in the design grid.

Field List

Design Grid

Field Name Row in Design Grid

If the query you are creating includes all the fields from the specified table, you could use one of the following three methods to transfer all the fields from the filed list to the design grid.

Field List

Design Grid

Click and drag each field individually from the field list to the design grid. Use this method if you want the fields in your query to appear in an order that is different from the order in the field list.

Field List

Design Grid

Double-click the asterisk in the field list. Access places the table name followed by a period and an asterisk (as in “Costomer.*”) in the design grid.

Field List

Design Grid

This signifies that the order of the fields will be the same in the query as it is in the field list. Use this method if the query does not need to be sorted or to have conditions for the records you want to select.

Field List

Design Grid

The advantage of this method is that you do not need to change the query if you add or delete fields from the underlying table structure. They will appear automatically in the query.

Field List

Design Grid

With the third method, Double-click the field list title bar to highlight all the fields, and then click, hold and drag one of the highlighted fields to the design grid.

Field List

Design Grid

Access places each field in a separate column and arranges the fields in the order in which they appear in the field list. Use this method rather than the previous one if your query needs to be sorted or to include record selection criteria.

Sorting Data in a Table Query or Form

The Sort Ascending and Sort Descending buttons on the toolbar allow you to sort records immediately, based on the selected field. First you select the column on which you want to base the sort, and then click the appropriate sort button on the toolbar to rearrange the records in either ascending or descending order.We will see other sorting methods later.

Filtering Data in a Table Query or Form

A filter is a set ofrestrictions you place onthe records in an opendatasheet orform totemporarilyisolate asubset of therecords.

Filtering Data in a Table Query or FormA filter lets you viewdifferent subsets ofdisplayed records soyou can focuson only thedata you need.

Filtering Data

Unless you save a query or form with a filter applied,an applied filteris not availablethe next time yourun the query oropen the form.

Filtering Data

The simplest technique for filtering records is filter by selection.

Filter By Selection lets you select all or part of a field value in a datasheet or form, and then display only those records that contain the selected value in the field.

Filtering Data

Another technique for filtering records is to use Filter By Form.

Filter by Form changes your datasheet to display empty fields. Then you can select a value from the list arrow that appears when you click any blank field to apply a filter that selects only those records containing that value.

You can also right clickright click in a field to activate a pop-up menu that will give you filtering and sorting options.

With this method, you also have the Filter Filter Excluding SelectionExcluding Selection, which is the opposite of Filter by Selection.

You also have the Filter For optionFilter For option, which allows you to type in the criteria that you are looking for.

Page AC 3.19

Quick Check Review

Quick Check Review

Let’s take a fewmoments to break upinto discussion groups.Each group will discussthe quick check questions on page AC 3.19 in your books. We will then review the answers at the end of the discussion.

Quick Check Review Session 3.1

1) What is a select query?

Quick Check Review Session 3.1

2) Describe the field list and the design grid in the Query window in Design view.

Quick Check Review Session 3.1

3) How are a table datasheet and a query datasheet similar? How are they different?

Quick Check Review Session 3.1

4) The ______ is the “one” table in a one-to-many relationship, and the _______ is the “Many” table in the relationship.

Quick Check Review Session 3.1

5) _______ is a set of rules that Access enforces to maintain consistency between related tables when you update data in a database.

Quick Check Review Session 3.1

6) For a date / time field, what is ascending sort order?

Quick Check Review Session 3.1

7) When must you define multiple sort keys in Design view instead of in the query datasheet?

Quick Check Review Session 3.1

8) A(n) ________ is a set of restrictions you place on the records in an open datasheet or form to isolate a subset of records temporarily.

You have been requested to help the people at Valle Coffee with creating queries that will answer their questions.

Your assignment today is to do the class exercises starting on Page AC 3.01 through page AC 3.19

Disk to use for this Class:

Tutorial 3 Level 1 Disk 1 Restaurant .mdb

Class # 3 Tutorials

Tutorial 3 beginning on Page AC 3.01 and working through Page AC 3.19