Records

How do I sort records generally?

When sorting records in table datasheet mode, please note the method is slightly differently to Microsoft Excel. As you are probably familiar with, in Microsoft Excel, you have to highlight all the columns in the spreadsheet, choose the Sort... menu command, specify the column(s) for sorting, choose ascending or descending, and click the Sort button.

In Microsoft Access, you don't have to worry about selecting all the columns. When sorting records, highlight the column you want the sorting to take place. For example, if you have a field name called Last Names and you want to sort the records by last names in ascending order, highlight the Last Name column. Highlighting involves clicking on the field name at the top of the column.

Now to tell Microsoft Access to sort the records, choose the menu command Records>Sort>Sort Ascending or Records>Sort>Sort Descending. All the records are moved around such that the data in the column you want sorted will appear in the correct order.

How do I find records generally?

Finding records in table datasheet mode is called filtering in the language of Microsoft Access.

The best way to understand how to filter records is by an example. Suppose you want to find duplicate records for an individual. To find such records, you could search by the individual's last name and see whether Microsoft Access will filter out more than 1 record satisfying the correct criteria in the Last Name field (or column).

Suppose you have a particular last name in mind, say Smith. To see how many records have the same last name, look at the Last Name column. Go down the column and find the first instance of the name Smith. This will become our criteria for Microsoft Access to search or filter by. Click the right button on the mouse while the cursor is on top of the last name you have chosen (i.e. Smith). A pop-up menu appears. Choose "Filter by Selection". Microsoft Access automatically filters out and displays all the records satisfying the criteria. In other words, all records having the last name "Smith" will be shown.

Do you want to do further filtering on the filtered records? No problems. Choose another cell you want to search by (can be in any other column) in the filtered table and select "Filter by Selection" in the same way. The table will get smaller and more refined to suit your type of analysis.

For slightly more sophisticated and flexible filtering, it is recommended you choose Records>Filter>Advanced Filter/SortÉ in the menu command. This gives you full control of filtering/searching as well as sorting records. By choosing this advanced filtering mode, you will be shown a filtering window with Table 1 appearing in the top half (this is Microsoft Access' way of saying it knows which table you are referring to). In the lower half of the window is the place where you can make any number of filters (recorded in columns) to take place simultaneously.

For simplicity sake, let us do a simple filter. Suppose you want to filter out those records whose date of birth of the individuals are after 1/1/1980. To create this filter:

1. Click into the white box next to the word "Field:". A little arrow will appear in the small grey box letting you know there is a pop-up menu for selecting your preferred field.
2. Choose the "Date of Birth" field from the pop-up menu (assuming your table has a field with this name).
3. You have the choice of sorting the filtered (or found) records in the box below (next to "Sort:"). The default value is to sort in ascending order. But you can choose no sorting whatsoever.
4. Now comes the critical part of the filtering process. You have to tell Microsoft Access what it is you are looking for. Click into the white box next to "Criteria:"
5. Type ">1/1/1980" (without the quotation marks).
6. When you are ready to tell Microsoft Access to filter and sort, go under the Filter menu command and choose Apply Filter/Sort...

With a bit of luck, the records should be filtered (and sorted) exactly as you wanted.

It is not sorting my records properly. What's happening?

Microsoft Access, although powerful in the things it can do, is actually a pretty dumb program. It doesn't know the field type in the column you have selected is wrong. For example you may have the Date of Birth field defined in Table Design mode as Text when in fact it should be in the Date format.

Change the field type. Otherwise the sorting procedure will not work and Microsoft Access will be left bewildered in what it is you want to do.