MS Access 2003 - Lesson 21: Chapter 6: Using Query to check data

In the previous chapter, you learned how to sort and filter data to better manage the information in the table. In this chapter, you will learn how to filter and organize data, which is a powerful tool provided by Access - Queries. Like the filter, Access queries allow you to select data from the table. However, queries work harder than filters.

At the end of this chapter, you will understand the following:

1. What are the queries?
2. What types of queries Access allows to create.
3. How to store queries.
4. How to use Query Wizards
5. How to design your own queries
6. How to use QBE grid
7. How to set properties in a query.

Lesson 21: Learn about Queries

Under normal English words, a query is a question. In fact, it is also a query in Access. In short, Access allows you to ask a question designed to return a number of records from a table. For example, 'show orders entered in the last 30 days that cost more than 5000 $'. This is a query, and you can create it easily in Access. Although you cannot speak queries in frequently used conversations, Access gives a scope in determining which records you want to see.

Types of queries

Access supports 5 different types of queries. Each query performs a different operation in relation to the data in the table. Here are 5 types of queries:

• Select query . This is the simplest type of query you can create. A select query returns the number of records based on the criteria you specify. In many cases, this type of query is similar to a filter (back to the previous chapter).

• Action query . This query takes action on the recovered data. For example, the query can create a new table and transfer records to it, or you can delete the records in the current table.

• Crosstab query . This query gives information in a spreadsheet - the same management as the Datasheet. (Crosstab is a word made by cross-tabulation). You can use this query to aggregate and sum up based on the contents of different fields.

• Parameter query . This query works in relation to other queries. When executing this query, Access asks you to set up questions. The query uses the answers to complete its work. You can think of this type of query as a 'query with the user interface.'

• SQL query : To determine the type of query, you must use SQL (Structured Query Language). SQL provides a way to communicate with different types of databases or database designs on the network.

You can give an opinion from viewing this list of queries so that you can use the query more than just questions about the data (and you have to do it right). The query can do more and more. The type of query you use depends on what you want to accomplish. When practicing this chapter, you will learn how to create different types of queries.

As you know, Access allows queries to the information in the table. In addition, it also allows querying information in queries. While this may sound unnecessary, implementing a query is a powerful and convenient operation. A query usually returns a subset of information in a table; in fact, this subset looks like a normal table, although it is a table based on another table. You can use subset as the basis for further queries. Therefore, you can query data from a table and query itself. Even query the results of the second table! Access is quite powerful and flexible when executing queries.

How Access stores queries

In Access, you save queries to the database; they are other components of the database, another object like tables, reports, or macros. In chapter 5, 'Sorting and filtering information', you learned how to save a filter as a query, and you saved it under a name. You can see which queries have been defined for the database by clicking your mouse on the Queries button in the Database window. When working with a new database or a database where queries have not been defined, Access displays no queries in the window without the options that allow you to create new queries, images. first.

Figure 1: Database window with the selected Queries button

Note that when you click your mouse on the Queries button, Access does the same as the available buttons when you click your mouse on the Tables button. In Access, you perform many of the same actions to control queries like those with tables. For example, you can copy queries, rename and delete them. You can also create a new query and change the existing queries.

Access gives you two ways to create a query. You can use one of the Wizards queries or create a query from scratch. In the last part of this chapter, you will learn how to create queries using both methods.