How To Sort And Filter A Table In Access 2010
Fri 23rd September 2011
Suppose you've created a table in Access 2010, for example containing names and addresses of a number of staff. The headings might include Ref, Firstname, Surname and Town. The Ref field is the primary key. You then display the table in normal datasheet view. The records are displayed in Ref order.
You can now sort and filter the table in this view using commands on the Home tab. For example suppose you want to sort the table in Surname order. To do this click into any cell in the Surname column, and then in the Sort and Filter commands on the Home tab click the A-Z command. The table is sorted in Surname order.
If you want to keep this new sort order, save and then close the table. If you open the table again you'll see that the data is still sorted in Surname order. You can remove this new sort by clicking the Remove Sort command and the original sort order is restored.
You can filter the table in two main ways, Filter by Selection and Filter by Form. We'll look at Filter by Selection first. To do this, first select any piece of data in a cell. This could by the whole cell or part of the data in the cell. Then in the Sort & Filter group click the Selection command. Four options let you choose from Equals to, Does not equal to, Contains, and Does not contain. Choose one and the table is filtered using your choice.
You can if you wish then choose a selection in the resulting display and again apply Filter by Selection, so you can filter for multiple selections. To clear all the filters, click the Toggle Filter command in the same group. By the way of you save the table whilst filtered, the filter will not be saved. Try this and you'll find that if you then re-open the table you'll see that the full list of records display. Next we'll look at filter by form, so ensure the table is open with all records showing.
To Filter by Form click the Advanced command in the same group and choose Filter By Form. The display changes to only show the field headings with one empty data row under the headings. If you click into any of the cells under a heading you'll see a pop down displaying all the values of data in the table.
Choose one, for example one of the Town names. Then click the Toggle Filter command in the upper Tab, and the data is filtered to show only records for that town. You'll see a filter icon in the field heading to remind you which field is filtered. You can also use Filter by Form using more than one filter. To do this, click Toggle Filter again, and the Filter by Form is displayed again with your town selection. You can then select an additional field, make a selection, and again click Toggle Filter. Now the data is displayed with the two filters applied.
In the Filter by Form view you can also use wildcard symbols. To show this you might like to clear all the current filters by clicking on Advanced Filter and choosing Clear all Filters. Then once again choose filter by form. This time into the cell under the Town field, and type the first letter of one of the towns followed by an asterisk symbol *. The * symbol means "anything", the combination of the first letter of the town name and the asterisk means show all towns with the name beginning with the letter you typed.
Don't forget that although these quick filters are extremely useful, you are not able to save the various criteria. So these filters are excellent for a one time use. One advantage of the Filter by Form feature is that, if you want to, you can convert the filter criteria into a query. To do this first apply a Filter by Form of your choice. Then to convert to a query, click the Advance command in Sort & Filter and choose Advanced Filter/Sort. The view changes into a new Query by design view, with your filter criteria already in the design. You can then save the query and run it in the usual way to apply the filter again at any time.
Interested in learning more about Access 2010? You might like to consider attending a training course. There are lots available and this can be a really effective way to increase your Access skills in a short time.
Original article appears here: