CREATING QUERIES
Lesson 9
Introduction
The real power of an Access 2007 database is in the 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 very own table that you can analyze and manipulate further. This lesson will explain how to plan a query using a three-question planning process. You will learn how to use the Query Design command to run the query, as well as how to modify the query to hide fields or other information in your query results. Finally, it will show you how to save the query for later use.
Using Queries
Queries retrieve information from one or more tables based on a set of search conditions that you set up and then combine that information in a way that is easy for you to analyze. If you have used an Advanced Filter in Access 2007, then you have already run a very basic query on only one table. If you want to pull data from more than one table, though, you will need to use either the Query Design command or the Query Wizard.
Before using the Access 2007 query tools, it is 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 you are planning a query:
 What do you want the results to look like? Identify every field or bit of information that you want included in the results.
 Where is the information stored in the database? List which tables (and/or queries) hold the information that 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 new technology series coming out soon, and we want to send coupons to customers that 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:

Fields in Results
 In which tables is the information stored? Well, for this query we'll need: o Customers table - to get the customers' names and addresses o Books table - to know which books are technology books o 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.

Criteria of the Query Condition
Using the Query Design Command
Once you have 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
To build and run a query using the Query Design command:
 Select the Query Design command from the Create tab on the Ribbon.

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 Dialog Box

 Drag and drop the fields you want to see in your results to the bottom portion of the query design screen.

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 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 Command
 Finally, view your results to determine if they match your desired 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
To hide a field in your query results:
 In the query design window, de-select the Show option by clicking on it.

Show/Hide Field
 Now, when you run your results, that field will be hidden, as seen below.

Query Results with Category Field Hidden
Saving the Query
Sometimes you will not need to save your results or your query design. Other times, you may want to keep it to run again later or to modify it slightly. Saving a query is very easy to do.
To Save a Query
To save the query for later use:
 Right click on the query tab.
 When the Save As dialog box opens, give your query a meaningful name.

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

CREATING OTHER QUERIES AND ACTION QUERIES
Lesson 10
Introduction
You already know how to plan and run a basic query. This lesson will show you how to run a query that includes a Totals function to group and count the records in the results. It will also deal with ways to further sort and filter via your query design to refine the results even more.
Using Totals in a Query
Sometimes, you may want to see your query results grouped or counted in some way. Access 2007 offers several options that make these functions possible. Perhaps the easiest of these is the Totals command, whose optional functions are very similar to the functions used in Microsoft Excel. These functions include:
 Sum -- used to add a column of numbers.
 Average -- used to find the average of a column of numbers.
 Maximum -- returns the highest value in a field.
 Minimum -- returns the lowest value in a field.
 Count -- used to count the number of same values in a query.

One of the most useful Totals functions to use in queries is the Count function.
Using Count and Group By in a Query
When you use the Totals command in a query, Access will automatically group every field by the values in each field. This just means that it will look for repeating values and group the like values together, so they appear as one record, rather than as many records. This is called the Group By function.
Take our bookstore database for example. If we run a query to see the information for every book that has been ordered, we'd get a list that looks like this:
Query for Ordered Books
Notice that we get a record back for every order of each book that has been ordered.
In our bookstore example query, we want to see these titles grouped together, so that we see each ordered titles only one time. To do this, we use the Count and Group By options.
To Use the Count and Group By Options in a Query
To include the Group By and the Count functions in a query:
 Click on Totals in the Show/Hide group on the Ribbon.

Totals Command
The Total row will instantly appear in the bottom portion of the query design screen.
Total Row in Query Design
 Next, click in the Totals row for the field you wish to count. We want to count the number of times the same Book ID appears in the Orders table.
 From the list of optional Totals functions, select Count.

Total Options in Query Design
 Click Run! to see your results. Notice that each title, author, price and category is now listed only one time for each book, with an extra column that indicates the number of times this Book ID appeared in the Orders table.

Count and Group By Functions in Query Results
Using Count and Group By in a Query
When you use the Totals command in a query, Access will automatically group every field by the values in each field. This just means that it will look for repeating values and group the like values together, so they appear as one record, rather than as many records. This is called the Group By function.
Take our bookstore database for example. If we run a query to see the information for every book that has been ordered, we'd get a list that looks like this:
Query for Ordered Books
Notice that we get a record back for every order of each book that has been ordered.
In our bookstore example query, we want to see these titles grouped together, so that we see each ordered titles only one time. To do this, we use the Count and Group By options.
To Use the Count and Group By Options in a Query
To include the Group By and the Count functions in a query:
 Click on Totals in the Show/Hide group on the Ribbon.

Totals Command
The Total row will instantly appear in the bottom portion of the query design screen.
Total Row in Query Design
 Next, click in the Totals row for the field you wish to count. We want to count the number of times the same Book ID appears in the Orders table.
 From the list of optional Totals functions, select Count.

Total Options in Query Design
 Click Run! to see your results. Notice that each title, author, price and category is now listed only one time for each book, with an extra column that indicates the number of times this Book ID appeared in the Orders table.

Count and Group By Functions in Query Results
Sorting and Filtering Query Results
Once you have the results looking how you want them, you can sort and filter them to narrow your results down even further. This can be done using the methods of sorting and filtering covered in Lesson 10 and Lesson 11, or by applying the sort and filter in the query design itself.
To Sort via the Query Design
To add a Sort to any field in the query design:
 Click on the Sort row for the field you wish to sort. A drop down list will appear:

Sorting via Query Design
 Choose one of the options: o Ascending will show your results sorted with the lowest numerical value or the text value closest to A first. o Descending will show the results with the highest numerical value or the text value furthest from A first. o (not sorted) will keep your records grouped, but not sort them.
 Click Run! to see the results.

To Filter via the Query Design
To add a filter to your query design:
 Click the Criteria row in the query design.
 Add your filter criteria by typing the value in the cell, as we did in the following example:

Filtering via Query Design

 Click Run! to see the results.

Writing an Expression
The following steps show you how to compute the Employees Income of each Employee.
1. In the Design View, click the horizontal scrollbar to view the Gross Pay field.
2. Click the GrossPay field, and then type this expression:

GrossPay: ([DaysWorked]*[PayRate])
3. After typing the expression, click the Query View button to display results.

Creating a Criteria
Using the Criteria row in a query grid you can write an instructions to find a specific group of records or limit the resulting dynaset to the records that you want to work with – for example, displaying the List of Highest Paid Employees.
The following step shows you how to write a condition or criteria to limit the result of the dynaset.
1. In the Query Design View window, click the horizontal scrollbar to view the GrossPay field.
2. Click the Criteria row in the column of the query design grid that displays the GrossPay field and then type >=10000.
3. Click the Query View button to view the dynaset. Access displays only those records whose Gross Pay value is greater and equal to 10000.

What is an Action Query?
Action Query is a query that makes changes or performs a specific action on a table in just one operation. There are four types of action queries and these are Make-Table, Delete, Append and Update Queries.
Make-Table Query
A Make-Table query creates a new table from a selection of records in one or more tables. You can use Make Table queries for several reasons, these are:
 For creating a new table to export data to another database. For example, you might want to create a new table that will contain Highest Paid Employees. Instead of exporting the entire Employees Info table, you can create a Make Table query that moves only the information needed by the Head Department.
 For creating a new table based on the information on a table or group of tables available at a certain point in time.
 For creating a backup copy of a table.
 For increasing the performance of form or report based on a multi-table queries. For example, suppose you want to generate a dynaset for a report or form that are based on a three-table. Instead of retrieving these data from the three tables, you can use the Make Table to create one table that will retrieved or stored all the data necessary for the form or report.

Delete Query

 A Delete Query deletes a group of records from one or more tables. For example, you can use a delete query to remove all the male Employees from the Employees Info table. Remember that once you have performed this operation, you cannot undo the deletion.

Append Query
 An Append Query adds a group of records from one or more tables to the end of one or more existing tables. For example, suppose that you entered new records in the Employees Info and Employees Income tables and then you want this new record to be added to other table. Instead of retyping this new record in the other table, you can use the Append query. When you run the Append query, it will take the records from two tables and matches the fields in the other tables. All the records are appended in the matching fields and ignore the fields that do not match.

Update Query
 An Update Query allows you to make global changes to the data in one or more tables. For example, in the previous discussions, you have learned how to create a query to calculate the Gross Pay of each Employee. But, if you view the Employees Income table you will notice that the data in the Gross Pay field is still 0 (zero). If you want to update the Employees Income of each Employee, then use the Update query.