Managing Data

Managing Data

After you add data to your database, you can use the sort, filter, and query features in Access 2002 to organize the data in more meaningful ways.

Sorting data

Access 2002 displays information in a table in alphabetical or numerical order based on the primary key. However, you can sort information so that it appears in an order that works best for you. For example, if you want to organize ships alphabetically, you can select the Ship Name field in your All Ships table and then click the Sort Ascending button.

In Datasheet view, you can sort all of a table's records in ascending or descending order, but you cannot use both sort orders on more than one field. When you sort in Design view, PivotTable view, or PivotChart view, you can sort records in ascending order by some fields and in descending order by others.

To sort records in Datasheet view

With the table open in Datasheet view, click the header of the field to sort so that the whole column is selected.

Note   If you select multiple columns, Access sorts records starting with the leftmost sele 16316u2014q cted column.

On the Datasheet toolbar, click the Sort Ascending button or the Sort Descending button .

Using filters

A filter is a set of criteria applied to data in order to display a subset of the data or sort the data. In general, you use a filter to temporarily view or edit records that contain a specific item. The rest of the records are then hidden from view. For example, if you want to find out how many ships sank in Lake Superior in the 1900s, you can use a filter to sort shipwreck data by location and chronologically at the same time.

In Access 2002, you can filter records in four ways:

Filter By Form.   Use this option when you want to choose the values you're searching for from a list without scrolling through all the records, or when you want to specify multiple criteria at once.

Filter By Selection.   Use this option when you can easily find and select the value you want the filtered records to contain.

Filter For Input.   Use this option when the focus is in a field and you just want to type in place the exact value you're searching for or the expression whose result you want to use as your criteria, or when you want to specify multiple criteria at once.

Advanced Filter/Sort.   Use this option to search for records that meet multiple criteria, search for records that meet one criterion or another criterion, or enter expressions as criteria.

To filter by form

With the table open in Datasheet view, on the toolbar, click the Filter By Form button to switch to the Filter by Form window.

Click the field in which you want to specify the criterion that records must meet to be included in the filtered set of records, and then in the pull-down list that appears, click the criterion. For example, click the Lake field, and then click Michigan in the list.

On the toolbar, click the Apply Filter button .

When you want to view the full table again, on the toolbar, click the Remove Filter button.

Note   When you run a filter, the Apply Filter button becomes the Remove Filter button.

To filter by selection

With the table open in Datasheet view, click the item that you want to use to filter data. For example, if you want the table to show only those ships that sank due to storms (so you can see what else those ships might have in common), click storm in any record in the table.

To use the other two methods of filtering, see Access 2002 Help.

On the toolbar, click the Filter By Selection button .

When you want to view the full table again, on the toolbar, click the Remove Filter button.

Using queries

Queries are great for viewing only certain data contained in a table. You establish a set of criteria when you create the query. Then, when you run the query, Access 2002 returns only those fields or records that are of interest to you. The results appear in a table in Datasheet view.

There are several types of queries in Access. A select query is the most common type. It does not change the data that it retrieves from tables, but after its results are displayed, you can update the records. You can also use a select query to group records and calculate sums, counts, averages, and other types of totals.

Select queries are similar to filters in that you can use either to:

Retrieve a subset of records from an underlying table or query.

Produce results that can be used as the source of data for a form or report.

Sort records.

Edit data if editing is allowed.

Whether you choose to use a query or filter depends on how you want to use the records that are returned. In general, you use a query if you want to do any number of the following:

View the subset of records without first opening a specific table or form.

Choose the tables containing the records you want to work with and add more tables at a later date if necessary.

The design grid is the grid that you use to design a query or filter in query Design view. For queries, this was formerly known as the QBE grid.

Control which fields from the subset of records appear in the results.

Perform calculations on values in fields.

You can filter records first, and then save the filter as a query. Through this method, you can bypass the query's design grid altogether (unless you want to make additional changes to the query). Even so, when you open the query in Design view, you'll see how Access filled in the design grid by using the information from the filter. You can then use this information as a guide for making additional changes.

Suppose you want to view a simplified version of your All Ships table to compare only certain types of information, such as the causes of shipwrecks and types of ships over time. The following procedure shows you how to create a select query to obtain this information.

To create a select query by using a wizard

In the Shipwrecks database window, under Objects, click Queries and then double-click Create query by using wizard.

In the Simple Query Wizard dialog box, enter the correct table or tables in the Tables/Queries box. For example, if you want to base your select query on the All Ships table, select that from the list.

For each field that you want to include in the query, click the field in the Available Fields box, and then click the forward arrow to move the field to the Selected Fields box. For example, add the Ship Name, Type of Ship, Year Sunk, and Cause of Sinking fields. Click Next.

In the appropriate box, type the name of the query (for example, type Shipwreck Causes) or accept the name that Access 2002 suggests. Click Open the query to view information, and then click Finish.

The query opens in Datasheet view. If you want to look at the design grid, switch to Design view by clicking the arrow next to the View button, and then clicking Design View.

After you create a query and save it in the database, you never need to create that query again. When you change or add new records, the new data automatically appears in the query the next time you run it. Likewise, whatever changes you make in the query appear in the full table.

To run a query

In the database window, under Objects, click Queries.

Double-click the query you want to run. Access 2002 runs the query and displays the results in Datasheet view.

SQL queries

For more information about creating SQL queries, see Access 2002 Help.

An SQL query is a query you create by using a Structured Query Language (SQL) statement. You can use SQL to query, update, and manage relational databases such as Access 2002.

When you create a query, Access automatically constructs the equivalent SQL statements for you. If you want, you can view or edit the SQL statement in SQL view. However, after you make changes to a query in SQL view, the query might not be displayed the way it previously was in Design view.

To view a query in SQL view

Run the Shipwreck Causes query. The results appear in Datasheet view.

On the toolbar, click the arrow next to the View button , and then click SQL View. The query is now in SQL view.