Locate specific records in a database

As your database grows, locating specific records will involve more than a quick glance at a datasheet. In this article, learn five ways to locate specific records based on your needs.

What do you want to do?


Browse through all records

You can browse through records by using the TAB key when you want to move through one record at a time, in order, to locate a specific record. You can also browse through records in a table in Datasheet view (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.) using the record navigation buttons. The record navigation buttons are available at the bottom of the table or form.

Navigation buttons

Callout 1 Go to the first record
Callout 2 Go to the previous record
Callout 3 Current Record box
Callout 4 Go to the next record
Callout 5 Go to the last record
Callout 6 Open a new (blank) record
Callout 7 Filter indicator
Callout 8 Search box

 Notes 

  • When you click in the Current Record box, you can type a record number, and then press ENTER to navigate to that record. The record number is counted sequentially from the start of the form or datasheet. It does not correspond to any field value.
  • To know if a filter has been applied, see the filter indicator button. If there is no filter applied or all the filters have been cleared, it displays No Filter. When it displays Filtered, you can click this button to remove the filter. Similarly, when it displays Unfiltered, you can click this button to apply the last filter that you used, if any.
  • When you enter text in the Search box, the first matching value is highlighted in real time as you enter each character. You can use this feature to quickly search for a record with a matching value.

Top of Page Top of Page

Go to a specific record

You can go to a specific record in Access when you know which record you want to find. The Go to box lets you choose a particular record from a drop-down list and is usually added to forms.

The Go to box

  • To navigate to a specific record, click the arrow to the right of the Go to box, and then select a record from the drop-down list.

 Note   If you know the first few characters of the record to which you want to navigate, you can type those characters in the Go to box to help you quickly find that record.

The Go to box displays only enough data to uniquely identify each record. When you select a record from the list, Access displays the rest of that record's data in the main area of the form.

Top of Page Top of Page

Search for a specific record

You can search for a specific record in a table or form by using the Find tab in the Find and Replace dialog box. This is an effective choice for locating a specific record when the record that you want to locate satisfies specific criteria, such as search terms, and comparison operators, such as "equals" or "contains".

 Note   You can only use the Find and Replace dialog box if the table or form currently displays data. This is true even if there are no visible records because a filter has been applied.

  1. Open the table or form, and then click the field that you want to search.
  2. On the Home tab, in the Find group, click Find, or press CTRL+F.

The Find and Replace dialog box appears, with the Find tab selected.

  1. In the Find What box, type the value for which you want to search.
  2. To change the field that you want to search or to search the entire underlying table, click the appropriate option in the Look In list.

 Tip   The Match list represents your comparison operator (such as "equals" or "contains"). To broaden your search, in the Match list, click Any Part of Field.

  1. In the Search list, select All, and then click Find Next.
  2. When the item for which you are searching is highlighted, click Cancel in the Find and Replace dialog box to close the dialog box. Records that match your conditions are highlighted

For more information about how to use the Find and Replace dialog box, see the article Use the Find and Replace dialog box to change data.

Top of Page Top of Page

Filter to see a limited number of records

You can filter to see a limited number of records when you want to see only the records that satisfy specific criteria and comparison operators. For example, to quickly narrow the records that are displayed, right-click a field whose value you want to match, and then select Equals, Does Not Equal, Contains, or Does Not Contain at the bottom of the shortcut menu. A filter can be turned on or off, which makes it easy to switch between filtered and unfiltered views of the same data. Unlike search, a filter only limits which of the records are displayed.

  1. To apply a filter that is based on a selection, open a table or form.
  2. To make sure that the table or form is not already filtered, on the Home tab, in the Sort & Filter group, click Advanced, and then click Clear All Filters, if that command is available.
  3. Navigate to the record that contains the value that you want to use as part of the filter, and then click inside the column (in Datasheet view) or control (in Form, Report, or Layout view). To filter based on a partial selection, select just the characters that you want.
  4. On the Home tab, in the Sort & Filter group, click Selection, and then click the filter you want to apply.
  1. To filter other fields based on a selection, repeat steps 3 and 4.

For more information about how to apply a filter, see the article Filter: Limit the number of records in a view.

Top of Page Top of Page

Create a query to find a specific record

The techniques that you can use to search and filter records are very useful for finding specific records for the case at hand. However, you might want to perform the same search or filter operation regularly. Instead of reproducing a set of search and filter steps every time, you can create a query (query: A question about the data stored in your tables, or a request to perform an action on the data. A query can bring together data from multiple tables to serve as the source of data for a form or report.). A query is a powerful and flexible way to locate specific records because it lets you perform customized searches, apply customized filters, and sort records. You can build your own queries to help you focus on specific records and to answer specific questions. Once created, a query can be saved and reused, and can also be used in building forms (form: An Access database object on which you place controls for taking actions or for entering, displaying, and editing data in fields.) and reports (report: An Access database object that you can print, which contains information that is formatted and organized according to your specifications. Examples of reports are sales summaries, phone lists, and mailing labels.).

When you use a query to search or filter, you use criteria based on the type of data for which you are looking. Different kinds of information are stored as different types of data. For example, birthdays are stored as date/time data, whereas names are stored as text data.

Sometimes, you cannot find a particular record that you know exists. This can occur if you are viewing records in a form or a query that does not display certain records because of the value of a particular field. The following example demonstrates how to use a query to find records.

Suppose that you are using a database created by using the Microsoft Office Access 2007 Issues template to track issues. The Issues table has a field that is named Status, which indicates whether a particular issue is active, resolved, or closed. You can create a query that displays issues whose status is closed by following these steps:

  1. On the Create tab, in the Other group, click Query Design.
  1. In the Show Table dialog box, double-click Issues, and then click Close.
  2. In the query designer, double-click the asterisk (*) in the Issues table. This helps make sure that the query will display all the fields from the records it returns.

Issues.* appears in the first column of the design grid, in the Field row. This indicates that all the fields from the Issues table should be returned.

  1. In the query designer, double-click Status on the Issues table.

Status appears in the second column in the design grid, in the Field row.

  1. In the second column of the design grid, clear the check box in the Show row. This helps make sure that the query does not display the Status field.

If you do not clear the Show check box in the Status column, the Status field will be displayed two times in the query results.

  1. In the second column of the design grid, in the Criteria row, type ="Closed". This is your search criterion. This is how you make sure that the query will return only those records where the value of Status is "Closed."

 Note   In this example, only one search criterion is used. You can use many search criteria for any given search by adding criteria to more fields, and by using the or row and the additional rows below or.

Your query is ready to run, and looks something like this:

Query design grid

  1. On the Design tab, in the Results group, click Run.

 Note   Unless you have already begun tracking issues and therefore have data in the Issues table — and you have set the status of at least one issue to "Closed" — the query will not return any results. However, you can save the query, and use it at any time in the future.

  1. Press CTRL+S to save the query.
  2. In the Save As dialog box, type a name for the query in the Query Name field, such as Closed Issues, and then click OK.

You now have a query that displays issues whose status is closed.

To learn more about how to create queries to help you find records, see the article Select data by using a query.

To learn more about search and filter criteria, see the article Examples of query criteria.

Top of Page Top of Page

 
 
Applies to:
Access 2007