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.

FIGURE 36-1
A typical three-table select query. This query displays the sales date, number of trucks, and type of truck for all trucks sold in the year 2007.

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.

FIGURE 36-2
A more complex query returning customers that purchased more than one car in 2007.

FIGURE 36-3
The resulting datasheet of the query shown in Figure 36-2.

• CDC swine flu numbers not so high as expected Surprisingly, the Centers for Disease Control and Prevention's swine flu tally for all 50 states plus the District of Columbia,...
• Wynn Macau to Open Diamond Suite Tower by 2010 Wynn Macau casino is one of the destinations that has made the island the number one gambling area in the...
• Camanche Lake Camanche Lake is located in: Ione, CA Phone: (209) 763-5121 About the Lake: Camanche Lake is in the heart of...
• 100 Thing challenge What do you think, could you do it? I have to admit I've pared down some this week -- as...
• Bullards Bar Lake Bullards Bar Lake is located in: Nevada City, CA Phone: (530) 288-3231 About the Lake: This gorgeous lake covers 4700...