Getting to Know Microsoft Access, Part 5: Retrieving and Arranging Data Meaningfully

Sorting

Records are automatically sorted by primary key. You will however often want to see records sorted another way. You can sort fields in ascending or descending order. To sort your table on a specific field, click into your designated field in datasheet view, and click the "Sort Ascending" or "Sort Descending" button on the toolbar. If you’ll be building a database in a language other than English you can set the sort order for that language in the Tools | Options menu option, under the General tab.

You can sort your records by more than one field. To sort by multiple fields, first make sure the fields you wish to sort by are adjacent to each other in the table. Next, highlight the two fields and sort them. If you save the datasheet when you are finished applying a new sort order the table will remain sorted that way even after you close the database.

Filtering

Sometimes you want to see only certain records; in these cases you can filter your form, datasheet, or subdatasheet according to the criteria you specify. Filtering doesn’t delete records, it just hides certain records from view so it’s easier for you to see the records you want to see. There are five types of filters in Access:

• Filter By Selection: Shows values identical to the one you selected. You can use filter by selection on any data type except OLE. Once you’ve selected the record or records you want to filter choose the Records | Filter | Filter by Selection menu option to apply the filter.
  
  
• Filter Excluding Selection: Shows only those records that don’t have the value you selected. Select the record or records you don’t want to see and choose the Records | Filter | Filter Excluding Selection menu option to exclude those records that match your selection.
  
  
• Filter By Form: Shows records based on the criteria you give in a blank table view. To filter by form choose Records | Filter | Filter By Form. The only difference between filtering by form and filtering by selection (above) is that in Filter By Form you choose the value to filter in a filter grid, instead of choosing a value from a datasheet. This grid looks like one table record that shows all the fields in your table. You can combine filter criteria to get a more interesting filter by selecting multiple values in the grid fields.
  
  
• Filter For: A shortcut menu box that allows you to enter filter criteria. To use Filter For, right-click the value you want to filter in the datasheet view and select the Filter For menu option: type in the expression you want to filter in the box next to the menu option. There are many types of expressions you can use to filter records with Filter For (and also with Filter By Form). I’m not going to cover them here, but you can do a search In Access help for "filter for" and then select the "Example of Filter Criteria" from the results to see the various types of commands you may use with Filter For and Filter By Form.
  
  
• Advanced Filter/Sort: Allows you to specify both a filter and a sort at the same time. The Advanced Filter/Sort lets you choose any and all of the Filter By Form options while simultaneously choosing sort options to apply to the table as well. Choose Records | Filter | Advanced Filter/Sort to use the Advanced Filter/Sort feature.

When you’ve applied a filter to an Access table the status bar, at the bottom, shows the acronym FLTR, meaning that a filter has been applied to the data. This way you know you are not viewing the whole dataset. To save a filter, make sure you first apply a filter, and then select the Save as Query toolbar button.

Basic Queries

Use filters when you need to see a subset of data temporarily, but use a query if you think you’ll need to use that subset at a later date. Queries are also preferable to filters when you need to perform calculations on your data. Note however that you can save a Filter By Form or a Filter By Selection as a query by choosing the Save as Query toolbar button after you apply a filter.

Using queries you can choose to see just a subset of records, and then only some of the fields in those records. A query basically means a question. You can query one or multiple tables in an Access database at once. Access provides several different types of queries; we’ll cover the most basic queries this week.

There are four general types of queries in Access: select, special purpose, action, and SQL-specific. Select queries are commonly used for getting information from one or more tables and displaying that information in a datasheet view. You can also use a select query to group records and to perform calculations on certain field values. Special purpose queries include crosstab queries. You use crosstab queries to summarize values from a field. Action queries are used to update or delete records from tables, make new tables, or add new records to an existing table. SQL-specific queries only use structured query language instead of a design grid, with SQL running in the background like the other types of queries. One type of SQL-specific query is a union query that combines fields from one or several tables into one field.

Select Queries

To create a new query choose Insert | Query. This will open the New Query dialog box as shown below:

Choose "Simple Query Wizard" to create your select query. You will be given a series of dialog boxes in which you will select the records and fields you want to use in your query; you must also enter a name for your query. You can include fields from any of your other queries or tables. In the first dialog box, select your desired fields from your other tables or queries and choose "Next." Choose to create a detail query with all the record data or a summary query that calculates totals based on the field values.

Just like with tables, you are given a datasheet view of your query that shows all the records returned from the specifications you selected, a design view that shows you the query structure, and a SQL view showing the SQL statements that comprise the query; every query uses SQL statements. Let’s take a look at the Design view for your query.

In the query design view you can see an upper pane showing the tables that comprise the query data and a lower pane with the Query By Example design grid that shows the elements of the query design. The design grid shows the following fields: Field, Table, Sort, Show, Criteria, and Or, as you can see in the following image:

The Field and Table fields show from which fields and tables the elements that comprise the query came from. The Sort, Criteria, and Or rows are the same as in the filter grid. The Show field determines whether the field is displayed in the query result or not: check or uncheck the box as you please.

The criteria field is especially handy for filtering the results of your query. The Criteria field allows you to specify exactly which records you want to see in the query by using specific operators. For example, you can use the LIKE operator in conjunction with the * operator to do something like this:

Like Cre*

Access will return anything that begins with the letters "Cre" and has additional letters after "Cre." Another example would be to use the OR operator to return either one value or another, such as the following:

DVD Or VHS

Moreover, you can use the AND operator, in combination with the greater than and less than operators to produce something like this:

>50 And <150

Other operators such as +,-,=,*,/,-,Is Null, Is Not Null, Between…And, and NOT can be combined similarly and work just as you would expect them to.
 
When you want to create complex criteria and you’re not sure how to do it, you can use the Expression Builder for help. Click into the cell where you want to put an expression and click the Build toolbar button in the Query Design view.

You can set the appearance and behavior of your query using the Query Properties dialog box. To do so put your insertion point in the table pane and press ALT-ENTER. You can modify a query at any time in the Query Design view. You can insert new fields, change the field order, and change field properties. You can add calculated fields to your query that are recomputed each time the query is run so you always have current data. Using custom calculations you create new fields by combining values in other fields like so:

[End Date] – [Start Date]

Or

[First Name]&” “&[Middle Initial]&” “&[Last Name]

When you created your simple select query you probably noticed several other options for creating queries in the Query Wizard such as: Find Duplicates Query, Find Unmatched Query, and Crosstab Query. A find duplicates query locates and shows records of the same value as your selected field. The find unmatched query shows records in one table that have no match in a related table. Finally, a crosstab query correlates summary values from two or more sets of field values. Choose the option you like best in the Query Wizard dialog box and follow the instructions as you did for the Simple Query Wizard.