The advantage of having direct access to the data tables is that you don't always have to write a query or a report in order to get to the data you want to see. Sometimes it is necessary to check for a specific record or alter something directly in the table, without having to write a SQL statement to do it. For example, imagine a colleague has told you a record was entered, but something on it needs to be changed and they do not have permissions to change it from the front end. When you open the relevant table you are confronted with over three thousand rows and need to find the exact record. Using filter and sort can help find the correct record on the table.

In this example I will use the Northwind database that comes with the Access programme, but feel free to use any table you want. Open the table by double clicking the name in the list. When the table opens, notice that the headings all appear along the top of the table in blue, with a small arrow next to the name. Click on the arrow next to the Last Name column. This will show a pop up box that contains all information necessary to filter.

The most obvious filter method is on the name itself. Whatever rows are in the table appear in an alphabetically ordered list with check boxes next to them. This means that the user can pick one or more specific names on the list and when the OK button is clicked only the chosen records will be visible. Be aware that this does not erase or change any information on the table. If you close the table down and re-open it, all records will be present as it is merely changing the ones that are visible at the current time. If you select the incorrect record then follow the same steps to bring up the selection again by clicking the arrow next to the column title. Change your selection as required. Note that if there is more than one record that is the same, for example more than one person with the same surname, this will only be shown once on the selector. If you pick 'Smith' and there are three people with that name then three records will be present in the viewer which makes it handy to select groups of data, for example all people labelled 'Active.'

While this method can be useful for searching groups, it is tedious if you are attempting to search for specific records among thousands. Even if they are alphabetical, you may need to scroll down the list and this will waste valuable time. Instead it is worth considering the Text Filters. Click the arrow next to the column title and choose text filters. A range of filters will be shown such as Equals, Begins With, Contains etc. Select an appropriate filter for your data. If you have one record with the surname Jones and one with Jordan then 'Begins With' Jo would return two results.

Once you have applied a filter then you can continue to apply more, but the new filters will only be applied to the data already returned. If you want to choose a new filter to run on all the original records then you must first remove the filters and begin from scratch. Click the arrow next to the Column name and choose 'Clear Filter From [column Name].' In the viewer all records should re-appear.

Filtering can also be a useful tool to find records that are missing information. Sometimes databases do not always force the user to enter data and incomplete records are accepted. This can become a major problem further down the line and data cleansing will be necessary. An example would be a simple CRM system where the client names have been entered without the Address. It is worth checking the database every now and again to check that this is not happening frequently. Use the filter to show the list and select 'Blanks' which will show all records where the field you are filtering on is blank. It would then be possible to copy and paste all the relevant data into an excel sheet to send to the department responsible for entering correct data.

It is also possible to sort the data and the sort can be saved when the table closes so that the table always re-opens with the sorting that you have designated. Click the arrow next to the column name and choose either 'Sort A to Z' or 'Sort Z to A'. This will force the data in the order you specify and can help when locating the information you require. It also means that reports will automatically be ordered in this way which may save a step later on.

It is worth remembering that all the things discussed in this article can be done with queries and with reports. The advantage to this is that a query or report is not directly accessing data and therefore no mistakes can be made by accidentally making changes to the live data. Working directly on the data at table level should only be attempted with care!