To sort data in Table view click anywhere in the field on which you want to sort the data and then click either the A-Z button or the Z-A button in the toolbar. This sorts the data on a single field, for example alphabetically by Artist or Title or numerically by Date Released.
Searching for a record is where you use the value of a field or fields to find a record or records whose data you want to view. You might, for example, want to find all the CDs released by a particular artist, all CDs on a certain label or all CDs released before a given date. In another database you might want to find a client's phone or fax number.
Searching is a quick way to locate information without committing yourself to a saved query. Unlike a filter or query operation a search does not display the records which match the search criteria in a separate table.
To carry out a search:
This opens the search dialogue box with the title bar set to the fieldname on which you chose to carry out the search:
Type in the text of the term you want to find, choose from the various settings and then click the Find Next button.
The options under the Search list are All, Up (from the last record to the top of the table) and Down (from the first record to the bottom of the table).
The options under the Match list are Whole Field, Start of Field (the first few letters such as 'English') and Any Part of Field (any text such as 'Prem').
You can use wildcards in your search terms, for example 'Parl*'.
You can use a similar technique to update records, for example to remove a mistake such as mis-naming a league (e.g. Series-A) or a change of name (e.g. English First Division to English Premiership). To do this proceed as before, inserting the cursor in the required field, but choose Edit/Replace (Ctrl/R) and fill in the slightly different dialogue box:
Filtering is the process of isolating records which match the criteria of a search. There are various ways to apply a filter:
To filter by selection move the cursor to the desired field and select the data there - for example, 'Van Morrison' in the Artist field. Click on the Filter by Selection button in the toolbar and the table or form will show only records which match this criterion.
To remove the filter and restore all records click the Apply Filter button which now turns off the filter.
Filtering by form allows you to select records with a search based on more than one field. Click on the Filter by Form button, choose the field and either write in the filter criteria or choose from a drop-down list if the field has a look-up.
The filter criteria used here were Label='CBS' and Category='folk'.
Click on the Apply Filter button again to restore the full table contents.
An advanced filter is a simplified query (see Section 5) with three things removed: the Show Table dialogue box (so you can only use one table), the SQL button and the Show Row option.
To start an advanced filter open a table or form and select Records/Filter/Advanced Filter/Sort. This opens the advanced filter dialogue box with the current table in the upper pane and an empty grid below.
To set up the filter drag field names from the box in the upper pane into the Field row in the lower pane. All fields will be displayed so you only drag those that you want to use for selection or sorting.
To display the filtered data click the Show Filter button (a plain filter). To change this filter select Records/Filter/Advanced Filter/Sort again.
To add conditions to a field click in the 'Or' row, for example Label='Parlophone' - this adds CDs on that label as well as CBS.
Advanced filters can be saved - choose File/Save As Query while the filter definition box is displayed.
To sort data on more than one field you should arrange the fields in the table so that the first field on which the data is to be sorted is to the left of the second field, the second to the left of the third, and so on. To sort the CDs by Title within Date Released within Artist the columns in the CDs table should be arranged so that Date Released is left of CDTitle, and Artist is left of Date Released.
We start out with this, the default design of the table:
We need to move the CDTitle field to the left of Date Released, which we do by clicking in the field title box to select it and then dragging the column to the desired position:
Now we select the three columns on which the sort will be based (Artist, Date Released and CDTitle) and click on the A..Z sort button:
And there we have it, sorted by CDTitle within Date Released within Artist. This reads backwards from the way the data appears but this is the way it is expressed. This puts first the Artists in order and then lists Date Released in order and finally CDTitle. (Actually there was no need to sort by CDTitle because none of the titles by a given artist was released on the same day so they appear in chronological order regardless of title.
Note that after rearranging the fields like this you will be asked, when you come to close the table, if you want to save the changes. If you click 'Yes' the fields will be left in their current positions and the underlying design will be changed. It is up to you whether you want to keep the new arrangement, but it is probably best to choose 'No' at this stage. You can always change the fields back to their original positions later but this creates extra work and is a potential source of confusion.