(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:
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.
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:
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.
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.
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.