Understanding Queries

A database’s primary purpose is to store and extract information. Information can be obtained from a database immediately after you enter the data or days, weeks, or even years later. Of course, retrieving information from database tables requires knowledge of how the database is set up.

For example, printed reports are often filed in a cabinet, arranged by date and by a sequence number that indicates when the report was produced. To obtain a specific report, you must know its year and sequence number. In a good filing system, you may have a cross-reference book to help you find a specific report. This book may have all reports categorized alphabetically by type of report and, perhaps, by date. Such a book can be helpful, but if you know only the report’s topic and approximate date, you still have to search through all sections of the book to find out where to get the report.

Unlike manual databases, computer databases like Microsoft Access easily obtain information to meet virtually any criteria you specify.

This is the real power of a database—the capacity to examine the data in more ways than you can imagine. Queries, by definition, ask questions about the data stored in the database. Most queries are used to drive forms, reports, and graphical representations of the data contained in a database.

What is a query?

The word query comes from the Latin word quærere, which means “to ask or inquire.” Over the years, the word query has become synonymous with quiz, challenge, inquire, or question. So, think of a query as a question or inquiry posed to the database about information contained in its tables.

A Microsoft Access query is a question that you ask about the information stored in your Access tables. You build queries with the Access query tools, and then save it as a new object in your Access database. Your query can be a simple question about data within a single table, or it can be a more complex question about information stored in several tables. After you submit the question, Microsoft Access returns only the information you requested.

Using queries this way, you ask the Access Auto Auctions database to show you only trucks that were sold in the year 2007. To see the types of trucks sold for the year 2007, you need information from three tables: tblSales, tblSalesLineItems, and tblProducts. Figure 36-1 is a typical Query Design window. Although it may look complex, it’s actually very simple and easy to understand.

After you create and run a query, Microsoft Access retrieves and displays the requested records as a datasheet. This set of records is called a recordset, which is the set of records selected by a query. As you’ve seen, a datasheet looks just like a spreadsheet, with its rows of records and columns of fields. The datasheet (of the recordset) can display many records simultaneously.

You can easily filter information from a single table using the Search and Filter capabilities of a table’s datasheet view (Filter by Selection and Filter by Form). Queries allow you to view information from a single table, or from multiple tables at the same time (as in Figure 36-1). Many database queries extract information from several tables.

Clicking the bottom portion of the View button on the Home tab of the Ribbon allows you to switch to the Datasheet view where you will now see the six records that match the query shown in Figure 36-1. This is a relatively easy-to-design query when you understand how to use the Access query designer. This simple query has many elements that demonstrate the power of the Access query engine: sorting a result set of records, specifying multiple criteria, and even using a complex Or condition in one of those fields.

You can build very complex queries using the same query designer. Suppose, for instance, that you want to send a notice to all previous buyers of more than one car in the past year that several new cars are available for auction. This type of query requires getting information from four tables: tblContacts, tblSales, tblSalesLineItems, and tblProducts. The majority of the information you need is in tblContacts and tblProducts.

In this case, you want Access to show you a datasheet of all Contact names and addresses where they have met your specified criteria (two or more cars purchased in 2007). In this case, Access retrieves customer names and cities from the tblContacts table and then obtains the number of cars from the tblProducts table, and the year of sale from the tblSales table. Figure 36-2 shows this relatively complex query. Access then takes the information that’s common to your criteria, combines it, and displays all the information in a single datasheet. This datasheet is the result of a query that draws from the tblContacts, tblSales, tblSalesLineItems, and tblProducts tables. The database query performed the work of assembling all the information for you. Figure 36-3 shows the resulting datasheet.

Types of queries

Access supports many different types of queries, grouped into six basic categories:

• Select: These are the most common types of queries. As its name implies, the select query selects information from one or more tables, creating a recordset. Generally speaking, the data returned by a select query is updatable and is often used to populate forms and reports.

• Total: These are special type of select queries. Total queries provide sums or other calculations (such as count) from the records returned by a select query. Selecting this type of query adds a Total row in the QBE (Query by Example) grid.

• Action: These queries enable you to create new tables (Make Tables) or change data (delete, update, and append) in existing tables. Action queries affect many records as a single operation.

• Crosstab: These queries can display summary data in cross-tabular form like a spreadsheet, with row and column headings based on fields in the table. The individual cells of the recordset are computed or calculated from data in the underlying tables.

• SQL: There are three SQL (Structured Query Language) query types—Union, Pass-Through, and Data Definition. These queries are used for advanced database manipulation, such as working with client/server SQL databases like SQL Server or Oracle. You create these queries by writing specific SQL statements.

• Top(n): Top(n) queries enable you to specify a number or percentage of records you want returned from any type (select, total, and so on) of query.

Query capabilities

Queries are flexible. They provide the capability of looking at your data in virtually any way you can imagine. Most database systems are continually evolving and changing over time. Very often, the original purpose of a database is very different from its current use.

Here is a sampling of what you can do with Access queries:

• Choose tables: Obtain information from a single table or from many tables that are related by some common data. Suppose you’re interested in seeing the customer name along with the items purchased by each type of customer. When using several tables, Access returns the data as a combined single datasheet.

• Choose fields: Specify which fields from each table you want to see in the recordset. For example, you can select the customer name, zip code, sales date, and invoice number from tblContacts and tblSales.

• Choose records: Select records based on selection criteria. For example, you may want to see records for only sellers in tblContacts.

• Sort records: You may want to sort records in a specific order. For example, you may need to see customers sorted by last name and first name.

• Perform calculations: Use queries to perform calculations on data. Perform calculations such as averaging, totaling, or counting fields and records.

• Create tables: Create a new table based on data returned by a query.

• Base forms and reports on queries: The recordset you create from a query may have just the right fields and data needed for a report or form. Basing a form or report on a query means that every time you print the report or open the form, you will see the most current information in the tables.

• Create graphs based on queries: Create graphs from data returned by a query.

• Use a query as a source of data for other queries (subquery): Create additional queries based on records returned by another query. This is very useful for performing ad hoc queries, where you may repeatedly make small changes to the criteria. In this case, the second query is used to change the criteria while the first query and its data remain intact.

• Make changes to tables: Access queries can obtain information from a wide range of sources. You can retrieve data stored in dBASE, Paradox, Btrieve, and Microsoft SQL Server databases, as well as Excel spreadsheets, text files, and other data sources.

How recordsets work

Access takes the records that result from a query and displays them in a datasheet. The set of records is commonly called (oddly enough) a recordset. Physically, a recordset looks much like a table. A recordset is, in fact, a dynamic set of records. The set of records returned by a query is not stored within the database, unless you have directed access to build a table from those records.

When you close a query, the query’s recordset is gone; it no longer exists. Even though the recordset itself no longer exists, the data that formed the recordset remains stored in the underlying tables.

When you run a query, Access places the returned records into a recordset. When you save the query, only the structure of the query is saved, and not the returned records. Consider these benefits of not saving the recordset to a physical table:

• A smaller amount of space on a storage device (usually a hard drive) is needed.

• The query uses updated versions of records.

Every time the query is executed, it reads the underlying tables and re-creates the recordset. Because recordsets themselves are not stored, a query automatically reflects any changes to the underlying tables made since the last time the query was executed—even in a real-time, multiuser environment.