Lesson 1

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:

1. Use the Find and Replace dialog box
2. Sort records using one or more fields
3. Filter records to display a subset of records in a table
4. Filter records using multiple criteria
5. Filter records using complex criteria

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.

Query by Example (QBE) is a method of creating queries in Microsoft Access without writing Structured Query Language (SQL) code. It uses a grid to specify the fields and criteria for the query. To create a QBE query, follow these steps:

1. Open the Access database that contains the tables you want to query.
2. Click the Create tab.
3. In the Queries group, click Query Design.
4. In the Add Table dialog box, select the tables you want to include in the query, and then click Add.
5. The tables will appear in the query design grid.
6. Drag the fields you want to display in the query results from the table list to the Field row in the grid.
7. To specify criteria for a field, click the Criteria row for the field and enter the criteria you want to use.
   

   

8. To specify sort order for a field, click the Sort row for the field and click Ascending or Descending.
9. To add a calculated field, click the Field row and type an expression in the Field box.
10. To save the query, click File > Save.

QBE is a simple and easy-to-use method for creating queries, especially for users who are not familiar with SQL. However, QBE can be limited for more complex queries. Here are some of the advantages of using QBE:

• It is easy to learn and use, even for users who are not familiar with SQL.
• It is a visual way to create queries, which can make it easier to understand and troubleshoot them.
• It is a good way to get started with creating queries, even if you plan to learn SQL later.

Advantages of QBE: Here are some of the disadvantages of using QBE:

• It can be limited for more complex queries.
• It can be less efficient than writing SQL code, especially for large datasets.
• It can be more difficult to troubleshoot errors in QBE queries than in SQL queries.

Overall, QBE is a useful tool for creating queries in Microsoft Access. It is a good option for users who are not familiar with SQL or for users who are creating simple queries. However, users who need to create more complex queries may need to learn SQL.

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.