Microsoft Access 2003

Microsoft Access 2003 - Specialist Chapter 4 PERFORMING QUERIES AND FILTERING RECORDS

Performance Objectives • Design a query to extract specific data from a database table • Use the Simple Query Wizard to extract specific data from a database table • Create a calculated field • Use aggregate functions in queries • Create crosstab, duplicate, and unmatched queries • Filter data in records by selection and by form

Performing Queries • Being able to extract (pull out) specific data from a table • one of the most important functions of a database • Extracting data in Access is referred to as performing a query • Query means to ask a question • You can: • design your own query • use a Simple Query Wizard • use complex query wizards

Designing a Query open the database file double-click click Queries button on the Objects bar

Designing a Query…/2 click click the table you want added When all tables have been added, click the Close button.

Designing a Query…/3 Query, Run OR click Run button drag the desired fields from the table to the Field text box type the criterion

Designing a Query…/4 Access searches the specified table for records that match the criteria and then displays those records. click to save

Designing a Query…/5 click type a query name

Criteria Returns Field Value “Smith” matching Smith “Smith” or “Larson” matching either Smith or Larson Not “Smith” that is not Smith “S*” that begins with S and ends in anything “*s” that begins with anything and ends in s “[A-D]*” that begins with A through D and ends in anything #01/01/2005# matching the date 01/01/2005 <#04/01/2005# less than (before) 04/01/2005 >#04/01/2005# greater than (after) 04/01/2005 Between #01/01/2005 And #03/31/2005 between 01/01/2005 and 03/31/2005 Establishing Query Criteria Access will automatically insert “ and #

Performing a Query on Related Tables click add tables drag desired fields enter criteria

Sorting Fields in a Query click arrow in desired field select sort order

Using the Simple Query Wizard open database file OR Insert, Query, Simple Query Wizard double-click click Queries button on the Objects bar

Using the Simple Query Wizard…/2 selected fields appear here click select table from drop-down list add/remove fields

Using the Simple Query Wizard…/3 specify type of query click

Using the Simple Query Wizard…/4 type a name select to modify design or insert criteria click

Using the Simple Query Wizard…/5 The wizard will automatically save the query.

Creating a Calculated Field A calculated control uses a mathematical equation to determine the contents that are displayed in the control object. select field Type field name followed by a colon(:) and then type equation. (Total:[Units in Stock]+[Units on Order])

Designing Queries with Aggregate Functions • Can be included in a query to calculate statistics from numeric field values • Sum • Avg • Min • Max • Count • Access displays one row in the query results datasheet with the formula result for the function used

Designing Queries with Aggregate Functions…/2 OR click Totals button View, Totals click arrow and click desired aggregate function

Changing Query Column Headings Click in the Field text box, and click the Properties button. click and type the new heading

Creating a Crosstab Query Calculates aggregate functions in which fields are grouped by two fields. Insert, Query OR click New button select click

Creating a Crosstab Query…/2 select desired options click

Creating a Crosstab Query…/3 select fields click

Creating a Crosstab Query…/4 select column headings click

Creating a Crosstab Query…/5 select functions for fields click

Creating a Crosstab Query…/6 type a name click

Creating a Find Duplicates Query Searches a specified table or query for duplicate field values within a designated field or fields. Insert, Query OR click New button select click

Creating a Find Duplicates Query…/2 select table click

Creating a Find Duplicates Query…/3 select fields that may contain duplicates click

Creating a Find Duplicates Query…/4 select fields to display click

Creating a Find Duplicates Query…/5 type a name click

Creating an Unmatched Query Compares two tables and produces a list of the records in one table that have no matching record in the other related table. Insert, Query OR click New button select click

Creating an Unmatched Query…/2 select table click

Creating an Unmatched Query…/3 select table that contains related records click

Creating an Unmatched Query…/4 select the matching field click

Creating an Unmatched Query…/5 select fields to display click

Creating an Unmatched Query…/6 type a name click

Filtering Data Places a set of restrictions on records (temporarily isolates specific records).

Using Filter By Selection OR Records, Filter, Filter By Selection click Filter By Selection button select specific data

Using Filter By Selection…/2 click to Remove Filter Records that match the selected data are displayed.

Using Filter By Form Records, Filter, Filter By Form OR click Filter By Form button

Using Filter By Form…/2 click Apply Filter button click in desired field click down-pointing arrow click desired option data can be filtered on two field values

Features Summary How do you display the New Query dialog box? Insert, Query OR click New button

Features Summary How do you use the Simple Query Wizard? Insert, Query, Simple Query Wizard

Features Summary How do you run a query? Query, Run OR click Run button

Features Summary How do you add a Total row to the Design grid? OR click Totals button View, Totals

Features Summary How do you start the Crosstab Query Wizard? Insert, Query OR click New button select

Features Summary How do you start the Find Duplicates Query Wizard? Insert, Query OR click New button select

Features Summary How do you start the Find Unmatched Query Wizard? Insert, Query OR click New button select

Features Summary How do you filter by selection? OR Records, Filter, Filter By Selection click Filter By Selection button

Microsoft Access 2003 - Specialist