Finding, Sorting and Filtering Data in Access Tables
Chances are that you are putting data in a database because you want to do something with it, that is analyze it, sort it, or use it to answer questions on how to run your organization more efficiently. It may be that some of what you want to do with your data can only be done with queries and reports, however, even tables give you some analytic tools.
This module covers the different ways you can manipulate data within tables. You will learn how to:
- Use the Find and Replace dialog box
- Sort records using one or more fields
- Filter records to display a subset of records in a table
- Filter records using multiple criteria
- Filter records using complex criteria
Queries extract Information
Queries extract information from a database. A query selects and defines a group of records that fulfill a certain condition. Most forms and reports are based on queries that combine, filter, or sort data before it is displayed. Queries are often called from macros or VBA procedures to change, add, or delete database records.
An example of a query is when a person at the sales office tells the database,
Show me all customers, in alphabetical order by name, who are located in Massachusetts and bought something over the past six months or
Show me all customers who bought Chevrolet car models within the past six months and display them sorted by customer name and then by sale date.
Instead of asking the question in plain English, a person uses the (QBE) query by example method. When you enter instructions into the Query Designer window and run the query, the query translates the instructions into Structured Query Language (SQL) and retrieves the desired data.
Data-entry and display forms
Data-entry forms help users get information into a database table quickly, easily, and accurately.
Data-entry and display forms provide a more structured view of the data than what a datasheet provides. From this structured view, database records can be viewed, added, changed, or deleted. Entering data through the data-entry forms is the most common way to get the data into the database table.
Data-entry forms can be used to restrict access to certain fi elds within the table. Forms can also be enhanced with data validation rules or VBA code to check the validity of your data before it is added to the database table.
Most users prefer to enter information into data-entry forms rather than into Datasheet views of tables. Forms often resemble familiar paper documents and can aid the user with data-entry tasks. Forms make data entry easy to understand by guiding the user through the fields of the table being updated.
Read-only forms are often used for inquiry purposes. These forms display certain fields within a table. Displaying some fields and not others means that you can limit a user's access to sensitive data while allowing access to other fields within the same table.