The best starting point for query creation’s the Design view. The following steps show you how it works. (To try this out yourself, you can use the BoutiqueFudge.accdb database that’s included with the downloadable samples for this chapter.) The final result—a query that gets the results that fall in the first quarter of 2007—is shown in Figure 6-6.

Here’s what you need to do:

Once you’ve created a query, you’ll see it in your database’s navigation pane (Figure 6-7). If you’re using the standard All Tables view, then the query appears under the table that it uses. If a query uses more than one table, then the same query appears in more than one group in the navigation pane.

Figure 6-7. By default, the navigation pane organizes your queries so they appear right underneath the table they use. For example, the TopProducts query (shown here) appears under the Products table. (You’ll learn how to change the way the navigation pane organizes itself in Section 14.1.)

You can launch the query at any time by double-clicking it. Suppose you’ve created a query named TopProducts that grabs all the expensive products in the Products table (using the filter criteria >50 on the Price field). Every time you need to review, print, or edit information about expensive products, you run the TopProducts query. To fine-tune the query settings, right-click it in the navigation pane, and then choose Design View.

Access lets you open your table and any queries that use it at the same time. (They all appear in separate tabs.) However, you can’t modify the design of your table until you close all the queries that use it.

If you add new records to a table while a query’s open, then the new records don’t automatically appear in the query. Instead, you’ll need to run your query again. The quickest way is to choose Home → Records → Refresh → Refresh All. You can also close your query and open it again, because Access runs your query every time you open it in Datasheet view.

	=1032

	="Harrington Red"

	<50 And >10

	<#1/1/2008# And >#12/31/2006#

Getting the top records

When you run an ordinary query, you see all the results that match your filter conditions. If that’s more than you bargained for, you can use filter expressions to cut down the list.

However, in some cases, filters are a bit more work than they should be. Imagine a situation where you want to see the top 10 most expensive products. Using a filter condition, you can easily get the products that have prices above a certain threshold. Using sorting, you can arrange the results so the most expensive items turn up at the top. However, you can’t as easily tell Access to get just 10 records and then stop.

UP TO SPEED: Filter Syntax

If filters seem uncannily familiar, there’s a reason. Filters have exactly the same syntax as the validation rules you used to protect a table from bad data (Section 4.3). The only difference is the way Access interprets the condition. A validation rule like <50 And >10 tells Access a value shouldn’t be allowed unless it falls in the desired range (10 to 50). But if you pop the same rule into a filter condition, it tells Access you aren’t interested in seeing the record unless it fits the range. Thanks to this similarity, you can use all the validation rules you saw in Section 4.3.1 to 137 as filter conditions.

In Chapter 7, you’ll learn how to beef up filter conditions with Access functions.

In this situation, the query Design view has a shortcut that can help you out. Here’s how it works:

1. Open your query in Design view (or create a new query and add the fields you want to use).

   This example uses the Products table, and includes the ProductName and Price fields.

2. Sort your table so that the records you’re most interested in are at the top.

   If you want to find the most expensive products, then add a descending sort (Section 3.2) on the Price field.

3. In the Query Tools | Design → Query Setup → Return box, choose a different option (Figure 6-8).

   The standard option’s All, which gets all the matching records. However, you can choose 5, 25, or 100 to get the top 5, 25, or 100 matching records, respectively. Or, you can use a percentage value like 25 percent to get the top quarter of matching records.

   Note

   In order for the Query Tools | Design → Query Setup → Return box to work, you must choose the right sort order. To understand why, you need to know a little more about how this feature works. If you tell Access to get just five records, then it actually performs the normal query, gets all the records, and arranges them according to your sort order. It then throws everything away except for the first five records in the list. If you’ve sorted your list so that the most expensive products are first (as in this example), you’re left with the top five budget-busting products in your results.

4. Run your query to see the results (Figure 6-9).

Figure 6-8. If you don’t see the number you want in the list, just type it into the Return box on your own. There’s no reason you can’t grab the top 27 most expensive products.

Figure 6-9. Here are the top five most expensive products.

Design view’s usually the best place to start constructing queries, but it’s not the only option. You can use the Query wizard to give you an initial boost, and then refine your query in Design view.

The Query wizard works by asking you a series of questions, and then creating the query that fits the bill. Unlike many of the other wizards in Access and other Office applications, the Query wizard’s relatively feeble. It’s a good starting point for query newbies, but not an end-to-end performer.

Here’s how you can put the Query wizard to work:

Behind the scenes, every query’s actually a text command written in an exotic language called SQL (Structured Query Language). SQL’s a staple of the database world, and it’s supported in all major database products, albeit with minor variations and idiosyncrasies.

As you craft a query in the design window (or using the Query wizard), Access generates a matching SQL command. When you save your query, Access simply stores the text of this command in your database. That text’s all Access needs to run the query later on.

Most of the time, you won’t spend much time contemplating the SQL that lurks under your queries’ surfaces. However, sometimes you may want to take a closer look. Here are some possible reasons:

To take a look at the SQL command for a query, right-click the tab title, and then choose SQL view. Figure 6-13 shows what you see.

Figure 6-13. Here’s the SQL command for the TopProducts query, which finds products that cost more than $50. If you’re intimidated, then you can jump back to another view at any time by right-clicking the tab title, and then choosing Design View or Datasheet View.

	1. SELECT Products.ID, Products.ProductName, Products.Price
	2. FROM Products
	3. WHERE (((Products.Price)>50))
	4. ORDER BY Products.Price;

	SELECT Products.ID, Products.ProductName, Products.Price,
	Products.Description
	FROM Products
	WHERE (((Products.Price)>100))
	ORDER BY Products.Price, Products.ProductName;

Creating a union query

The query designer doesn’t recognize some rare SQL tricks. You can use them only by editing the SQL command in SQL view, and once you’ve made the change, you can’t look at your query in Design view any longer (unless you remove the unsupported change later on).

A union query is one example of a query that’s occasionally useful but not supported by the query designer. A union query merges the results from more than one table, and then presents them in a single datasheet.

Essentially, a union query’s composed of two (or more) separate select queries. The trick’s that the results from each select query must have the same structure. So you need to retrieve similar columns from each table, in the same order. Assuming you can meet this standard, all you need to do is add the word UNION between the two queries.

Here’s a union query that presents a list of names drawn from two tables— Customers and Employees:

	SELECT Customers.FirstName, Customers.LastName
	FROM Customers
	UNION
	SELECT Employees.FirstName, Employees.LastName
	FROM Employees

This query works even though the structure of the Customers and Employees tables is different. The important part’s that the query results from both tables—in this case, the FirstName and LastName fields—match up.

Note

You can create a union query even if the column names differ—if the columns in the Employees table were F_Name and L_Name, the query would still work. Access simply uses the column names from the first query when it displays the results in the datasheet.

In this example, when you view the query results, you see a list of customer names followed by a list of employee names, although you can’t necessarily tell where one table leaves off and the other begins. You also can’t edit any of the data—union queries are strictly for reviewing information, not changing it. Access doesn’t let you edit union queries in the query designer. If you right-click the tab title, and then choose Design View, you wind up in SQL view instead.

Access puts union queries in the Unrelated Objects section of the navigation pane, and uses a different icon for them than for normal queries (Figure 6-14).

Note

If there are any duplicates in the results, union queries show just one copy. You can change this behavior by replacing UNION with UNION ALL. In the previous example, this step causes a person who’s both an employee and a customer to show up twice in the combined results.

Figure 6-14. Union queries appear in the navigation pane with a different icon. The two joined circles indicate that more than one set of results are being shown together.

Union queries are a good way to link together similar tables that have been separated for reasons of performance, security, or distribution. (See Section 18.2 for the different reasons you might split a single set of data into different tables.) Union queries aren’t a good way to work with parent-child relationships. For this task you need join queries, which are described in the next section.

WORD TO THE WISE: Think Twice Before Redesigning Your Tables

Access is surprisingly savvy at keeping track of which queries use particular tables. This trait becomes important when you crack open a table in design mode to change something about its structure.

Suppose you rename the Orders table to Sales, and the DatePlaced field to OrderDate. The next time you run the FirstQuarterOrders_2007 query (Figure 6-6), you find that—remarkably—it still works. Access knows that the FirstQuarterOrders_2007 query depends on the Orders table. When you change the names in the table, it adjusts the query accordingly.

Access includes a nifty tool that can look at any database object you choose, and tell you what other database objects depend on it. You can use this tool to figure out what queries, forms, and reports use the Orders table before you change it. To use this feature, follow these steps:

1. Choose Database Tools → Show/Hide → Object Dependencies. The Object Dependencies box appears on the Access window’s right side. (Choose the same command again when you want to hide it.)

2. In the navigation pane, select the database object that you want to examine.

3. In the Object Dependencies box, choose either “Objects that depend on me” (to see other objects that use this object) or “Objects that I depend on” (to see all the objects this object uses).

4. At the top of the Object Dependencies box, click the Refresh link. The Object Dependencies box lists all the appropriate objects, divided into categories by type (Figure 6-15).

Access can’t spot all dependencies, like when you need to delve into the SQL view to create a query that you can’t build in Design view. If you create a union query (as in the previous example), then Access isn’t smart enough to figure out what tables your query depends on. If you redesign those tables, then you’ll get an error the next time you run your query, saying that Access can’t find the right field or table. (To fix the error, you need to open your query in SQL view again, and change the field and table names to their new values.)

Figure 6-15. Here, the Object Dependencies box analyzes the Products table. It shows three tables that link to Products, and four queries that use the Products table. You can dig deeper into any object by clicking the plus (+) box next to its name. (Click the + next to TopProducts to check if any other database objects use that query.) The Ignored Objects section’s at the bottom of the list. The CustomersAndEmployees union query shows up here, and it tells you that Access has no idea what it depends on.

Access makes it remarkably easy to join two tables. The first step’s adding both tables to your query, using the Show Table dialog box. If you’re creating a new query in Design view, then the Show Table dialog appears right away. If you’re working with a query you’ve already created, then make sure you’re in Design view, right-click the window, and then choose Show Table.

If you’ve already defined a relationship between the two tables (using the relation-ships window, as described in Section 5.2.1, or by creating a lookup, as described in Section 5.2.5), then Access uses that relationship to automatically create a query join. You’ll see a line on the diagram that connects the appropriate fields, as shown in Figure 6-17.

If you haven’t already defined a relationship between the two related tables, then you probably should, before you create your query (see Chapter 5 for full instructions). But if for some cryptic reason you’ve decided not to create the relationship (perhaps the database design was set in stone by another, less savvy Access designer), then you can manually define the join in the query window. To do so, just drag the linked field in one table to the matching field in the other table. You can also remove a join by right-clicking the line between the tables, and then choosing Delete.

Figure 6-17. Access automatically joins the CustomerID field in the Orders table to the ID field in the Customers table, based on the relationship that’s defined in the database.

Once you have your two tables in the query design window and you’ve defined the join, then you’re ready to choose the fields you want. You can pick fields from both tables. You can also add filter conditions and supply a sort order, as you would with any other query. Figure 6-18 shows an example of a query that uses a join, and Figure 6-19 shows that same query in action.

Figure 6-18. This query shows information from the Orders and Customers tables. It doesn’t matter whether the first field’s from the Orders or Customers table—either way, you’re creating a list of orders with added customer information. Notice how the Table box (under the Field box) shows which table each field comes from.

Figure 6-19. You can easily see at a glance who ordered what. The ID column’s the order ID (although you could display the IDs from both the Customers and Orders tables).

Remember, when you link a parent and child table with a join query, you’re really performing a query that gets all the records from the child table, and then adds extra information from the parent table. For example, you can use a join query to get a list of orders (from the child table) and supplement each record with information about the customer that made the order. No matter how you create the join, you won’t ever get a list of customers with order information tacked on—that wouldn’t make sense, because every customer can make multiple orders.

Joins are one of the most useful features in any query writer’s toolkit. They let you display one table that has all the information you need.

The queries you saw in the previous example use what database nerds call an inner join. Inner joins show only linked records—in other words, records that appear in both tables. If you perform a query on the Customers and Orders tables, then you don’t see customers that haven’t placed an order. You also don’t see orders that aren’t linked to any particular customer (the CustomerID value’s blank) or aren’t linked to a valid record (they contain a CustomerID value that doesn’t match up to any record in the Customers table).

Outer joins are more accommodating—these joins include all the same results you’d see in an inner join, plus the leftover unlinked records from one of the two tables (it’s your choice). Obviously, these unlinked records show up in the query results with some blank values, which correspond to the missing information that the other table would supply.

Suppose you perform an outer join between the Orders and Customers tables, and then configure it so that all the order records are shown. Any orders that aren’t linked to a customer record appear at the bottom of the list, and have blank values in all the customer-related fields (like FirstName and LastName):

In this particular example, it doesn’t make sense for orders that aren’t linked to a customer to exist. (In fact, it probably indicates an order that was entered incorrectly.) However, if you suspect a problem, an outer join can help you track down the problem.

You can also perform an outer join between the Orders and Customers tables that shows all the customer records. In this case, at the end of the query results, you’ll see every unlinked customer record, with the corresponding order fields left blank:

In this case, the outer join query picks up three stragglers.

So how do you add an outer join to your query? Your start with an inner join (which Access usually adds automatically; see Section 6.3.1), and then convert it to an outer join. To do so, just right-click the join line that links the two tables in the design window, and then choose Join Properties (or just double-click the line). The Join Properties dialog box (Figure 6-20) appears, and lets you change the type of join you’re using.

Figure 6-20. The first option, “Only include rows where the joined fields from both tables are equal”, performs the standard inner join. The other two options let you create an outer join that incorporates all the unlinked rows from one of the two tables.

Finding unmatched records

Inner joins are by far the most common joins. However, outer joins let you create at least one valuable type of query: a query that can track down unmatched records.

You’ve already seen how an outer join lets you see a list of all your orders, plus the customers that haven’t made any orders. That combination isn’t terribly useful. However, the marketing department’s already salivating over the second part of this equation—the list of people who haven’t bought anything. This information could help them target a first-time-buyer promotion.

To craft this query, you start with the outer-join query that includes all the customer records. Then, you simply add one more ingredient: a filter condition that matches records that don’t have an order ID. Technically, these are considered null (empty) values.

Here’s the filter condition you need, which you must place in the Criteria box for the ID field of the Orders table:

	Is Null

Now, when Access performs the query, it includes only the customer records that aren’t linked to anything in the orders table. Figure 6-21 shows the query in Design view.

Figure 6-21. This query combines an outer join with a filter condition that matches only unlinked customer records. Notice the Show checkbox isn’t checked. That’s because the ID field’s used for a filter condition, but there’s no point in displaying it in the results datasheet.

Just as you’re getting comfortable with inner and outer joins, Access has another feature to throw your way. Many queries don’t stop at a single join. Instead, they use three, four, or more to bring multiple related tables into the mix.

Although this sounds complicated at first, it really isn’t. Multiple joins are simply ways of bringing more related information into your query. Each join works the same in a multiple-join situation as it does when you use it on its own. To use multiple joins, just add all the tables you want from the Show Table dialog box, make sure the join lines appear, and then choose the fields you want. Access is almost always intelligent enough to figure out what you’re trying to do.

Figure 6-22 shows an example where a child table has two parents that can both contribute some extra information.

Sometimes, the information you want’s more than one table away. Consider the OrderDetails table Boutique Fudge uses to list each item in a customer’s order. On its own, the OrderDetails doesn’t provide a link to the customer who ordered the item, but it does provide a link to the related order record. (See Section 5.4.2.2 for a discussion of this design.) If you want to get the information about who ordered each item, you need to add the OrderDetails, Orders, and Customers table to your query, as shown in Figure 6-23.

Figure 6-22. In this example, a list of products is amplified with product category information and supplier information. The Products table’s a child of both the ProductCategories and Suppliers tables, so this query uses both tables effortlessly.

Figure 6-23. If you want to find out who ordered each item, then you need to find the linked Orders table, and then continue to the linked Customers table. Even if you don’t want to show any data from the Orders table, you still need to follow this two-step process. The top figure shows the query that does this, and the bottom figure shows the result you’ll get when you run the query.

Multiple joins are also the ticket if you have a many-to-many relationship with a junction table (Section 5.3.2.1), like the one between teachers and classes. As you’ll remember from Chapter 5 (Section 5.4.1), the Cacophoné Studios music school uses an intermediary table to track teacher class assignment. If you want to get a list of classes, complete with instructor names, then you need to create a query with three tables: Classes, Teachers, and Teachers_Classes (see Figure 6-24).

Figure 6-24. Here’s how you define a list of classes that includes the name of the assigned teacher next to each class. The top query shows the design you need, and the bottom figure shows the result.