Learn Access Now! Chapter 5 Next Section in Chapter 6 Chapter 7
Chapter 6: Using Queries to Examine Your Data
(This is section 1 of 4 in this chapter)
In the previous chapter, you learned how you can use sorting and filtering to better manage the information in your tables. In this chapter, you will learn about another, more powerful tool provided by Access--queries. Like filters, Access queries let you select specific data from your table. However, queries are much more powerful than filters.
By the time you finish this chapter, you will understand the following key concepts:
- What queries are
- What types of queries Access lets you create
- How Access stores queries
- How to use the Query Wizards
- How to design your own queries
- How to use the QBE grid
- How to set properties in a query
Understanding Queries
In normal English, a query is a question. Effectively, that is also what a query is in Access. In short, Access lets you pose a question which is designed to return a number of records from a table. For instance, "show me the orders entered in the last 30 days that have a value over $5,000." This is a query, and it is one you can easily create in Access. Although you may not be able to state your query in the conversational style just used, Access gives you quite a bit of latitude in determining which records you want to view.
Types of Queries
Access supports five different types of queries. Each query type performs a fundamentally different operation in relation to the data in a table. These five types of queries are:
- Select query. This is the simplest type of query you can create. A Select query returns a number of records based on criteria you specify. In many instances, this type of query is very similar to a filter (covered in the previous chapter).
- Action query. This query performs an action on the data retrieved. For instance, the query could create a new table and move records to it, or it could simply delete records in the current table.
- Crosstab query. This query presents information in a spreadsheet-like manner--similar to the Datasheet. (Crosstab is a compound word formed from cross-tabulation.) You can use this query to calculate subtotals and totals based on the contents of various fields.
- Parameter query. This query works in conjunction with other types of queries. When you execute this type of query, Access asks you to answer a set of questions. The query uses the answers to those questions to finish its work. You can think of this type of query as a "query with a user interface."
- SQL query. To formulate some types of queries, you must use SQL (structured query language). SQL provides a way to communicate with other types of databases or specialized database servers on networks.
You may get the idea from looking at this list that you can use Access queries to do much more than just ask questions about data (and you would be right). Queries can do more--much more. The type of query you use will depend on what you want to accomplish with the query. As you work through this chapter, you will learn how to create many different types of queries.
As you know, Access lets you query information in tables. In addition, Access also lets you query information in queries. While this may sound redundant, querying a query is actually a powerful and useful operation. A query often returns a subset of information in a table; in effect, this subset is a table in its own right, even if it is a table based on another table. You can use this subset as the basis for further queries. Thus, you can query data from a table and then query the query. You can even query the result of the second query! Access is quite powerful and flexible when it comes to what you can actually query.
How Access Stores Queries
In Access, you save your queries with your database; they are another component of your database, another object, just like tables, reports, or macros. In Chapter 5, "Sorting and Filtering Information," you learned that you can save a filter as a query, and that, in doing so, you save it using a name. You can see what queries have been defined for a database by clicking your mouse on the Queries button in the Database window. When you are working with a new database or one that has no queries defined, Access displays nothing in this window except the options allowing you to create new queries, as shown in Figure 6-1.
Figure 6-1 A Database window with the Queries button selected.
Notice that when you click your mouse on the Queries button, Access makes the same toolbar buttons available as when you click your mouse on the Tables button. In Access, you perform many of the same actions to control queries as you do to control tables. For example, you can copy queries, rename them, and delete them. You can also create new queries and design (change) existing queries.
Access provides you with two ways to create queries--you can either use one of the query Wizards or you can create a query from scratch. In the rest of this chapter, you will learn how to create queries using both of these methods.
Note: For the examples in this chapter, you use the database you created in Chapter 4, "Creating Another Database." If you did not create that database and table directly, you may want to load it off of the CD-ROM; it is called Customer 1.mdb. You can save this file out under a different name, preferably Customer.mdb.
Learn Access Now! Chapter 5 Next Section in Chapter 6 Chapter 7