Access 2007

Using Queries to Make Data Meaningful - Part 1

Introduction

Access 2007 The real power of an Access 2007 database lies in its ability to pull data for quick analysis, which is what happens when you run a query. Queries allow you to retrieve information from one or more tables based on a set of search conditions you define. Access 2007 will display your results in their own table, which you can then further analyze and manipulate. In this lesson, we'll explain how to plan a query using a three-question planning process. You'll also learn how to use the Query Design command to run a query, as well as how to modify the query to hide fields or other information in your query results. Finally, you'll learn how to save the query for later use.

Using queries: Part 1

Download the example to work along with the video.

Using queries

Queries retrieve information from one or more tables based on a set of search conditions you set up and then combine that information in a way that's easy for you to analyze. If you've used an Advanced Filter in Access 2007, then you've already run a basic query on only one table. If you want to pull data from more than one table, though, you'll need to use either the Query Design command or the Query Wizard .

Before using the Access 2007 query tools, it's important to plan out the query using a logical process. Otherwise, you may not get the results you expect.

Planning a query

There are three questions you need to answer when planning a query:

  • What do you want the results to look like? Identify every bit of information—or field—you want included in the results.
  • Where is the information stored in the database? List which tables—and/or queries—hold the information you want to see.
  • What conditions do you want the data to meet? This helps determine how to set the criteria so Access can search the records properly.

Planning: Which customers ordered technology books?

Let's think about this process for our bookstore database scenario. We have a new technology series coming out soon, and we want to send coupons to customers who have ordered technology books from us in the past. A query can help us answer the question, Which customers have ordered technology books from us already? Let's use the three-question process to plan this query.

  • What fields do we want to see in the results? We need a list of customer names and addresses in order to mail the coupons to our customers, so we'll need the results to show the categories below:



    Query Plan Fields in Results
  • In which tables is the information stored? For this query, we'll need:
    • The Customers table to get customers' names and addresses
    • The Books table to know which books are technology books
    • The Orders table to know which customers ordered those books
  • What is the condition we want the data to meet? We want Access to look for only the books where the book's category is technology.
Query Condition Criteria of the Query Condition

Using the Query Design command

Once you've planned out your query, you can build and run it using Access 2007's query tools.

To build a query using the Query Design command:

  • Select the Query Design command from the Create tab on the Ribbon.


    Query Design Command Query Design Command
  • Use the Show Table dialog box to select which tables and/or queries to include in the query. Our plan called for all three tables.



    Show Table Show Table Dialog Box
  • Drag and drop the fields you want to see in your results to the bottom portion of the query design screen.



    Query Fields Add Fields to Query Design Screen
  • Enter the condition in the Criteria row for the condition field. For our query, we typed Technology in the cell labeled Criteria for the Category field. As seen above, Access 2007 puts quotation marks around the term to show that it is looking for exactly that term within the designated field.
  • Once the condition is set, click Run! in the Results group on the Ribbon.


    Run Query Run Query Command
  • View your results to determine if they match your desired results.



    Query Results Customers of Tech Books Query Results

Hiding fields or other information in the results

Sometimes the results of a query will include information that is seemingly unnecessary to you. Access 2007 allows you to easily hide these fields.

To hide part of the query result:

  • In the query design window, deselect the Show option by clicking it.


    Hide Field in Query Show/Hide Field
  • When you run your results, the field you chose will be hidden, as seen below.



    Query Results with Hidden Field Query Results with Category Field Hidden

Saving the query

Sometimes you will not need to save your results or your query design, and other times you may want to keep it to run again later or to modify it slightly. Saving a query is easy to do.

To save a query:

  • Right-click the Query tab.
  • When the Save As dialog box opens, give your query a meaningful name.



    Save Query Saving the Query
  • Click OK .
  • The query will now be listed in the object list on the left side of the Access window.

Challenge!

If you haven't already done so, save the sample Ready2Read database to your computer.

  • Plan a query to find out which customers order a certain category of books.
  • Use the Query Design command to set up the query.
  • Run the query, and view your results.
  • Save the query.
  • Modify the query to hide a field.