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.

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.

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.

	=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.

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.1) 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

   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, 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:

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 relationships window, as described in Section 5.2), 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-14.

Figure 6-14. 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.

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.

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-15 shows an example of a query that uses a join, and Figure 6-16 shows that same query in action.

Figure 6-15. 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-16. 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-17) appears, and lets you change the type of join you’re using.

Figure 6-17. 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-18 shows the query in Design view.

Figure 6-18. 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-19 shows an example where a child table has two parents that can both contribute some extra information.

Figure 6-19. 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.

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-20.

Figure 6-20. 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), like the one between teachers and classes. As you’ll remember from Chapter 5 (Section 5.4), 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-21).

To create a calculated field, you need to supply two details: a name for the field, and an expression that tells Access what calculation it must perform. Calculated fields are defined using this two-part form:

	CalculatedFieldName: Expression

For example, here’s how you can define the PriceWithTax calculated field:

	PriceWithTax: [Price] * 1.10

Essentially, this expression tells Access to take the value from the Price field, and then multiply it by 1.10 (which is equivalent to raising the price by 10 percent). Access repeats this calculation for each record in the query results. For this expression to work, the Price field must exist in the table. However, you don’t need to show the Price field separately in the query results.

You can also refer to the Price field using its full name, which is made up of the table name, followed by a period, followed by the field name, as shown here:

	PriceWithTax: [Products].[Price] * 1.10

This syntax is sometimes necessary if your query involves more than one table (using a query join, as described in Section 6.3), and the same field appears in both tables. In this situation, you must use the full name to avoid ambiguity. (If you don’t, Access gives you an error message when you try to run the query.)

To add the PriceWithTax calculated field to a query, you need to use Design view. First, find the column where you want to insert your field. (Usually, you’ll just tack it onto the end in the first blank column, although you can drag the other fields around to make space.) Next, type the full definition for the field into the Field box (see Figure 6-22).

Figure 6-22. This query shows two fields straight from the database (ID and Name), and adds the calculated PriceWithTax field. The ordinary Price field, which Access uses to calculate PriceWithTax, isn’t shown at all.

Now you’re ready to run the query. When you do, the calculated information appears alongside your other columns (Figure 6-23).

Calculated fields do have one limitation—since the information isn’t stored in your table, you can’t edit it. If you want to make a price change, you’ll need to edit the underlying Price field—trying to change PriceWithTax would leave Access thoroughly confused.

Figure 6-23. The query results now show a PriceWithTax field, with the result of the 10 percent markup. The neat part’s that this calculated information’s now available instantaneously, even though it isn’t stored in the database. Try and beat that with a pocket calculator.

Before going any further, it’s worth reviewing the rules of calculated fields. Here are some pointers:

Many calculated fields rely entirely on ordinary high school math. Table 6-1 gives a quick overview of your basic options for combining numbers.

You’re free to use as many fields and operators as you need to create your expression. Consider a Products table with a QuantityInStock field that records the number of units in your warehouse. To determine the value you have on hand for a given product, you can write this expression that uses two fields:

	ValueInStock: [UnitsInStock] * [Price]

	ExtendedDeadline: [DueDate] + 14

	ShippingLag: [ShipDate] - [OrderDate]

	LateDays: [DateSubmitted] - #03/20/07#

	TotalValue: [UnitsInStock] + [UnitsOnOrder] * [Price]

	TotalValue: ([UnitsInStock] + [UnitsOnOrder]) * [Price]

Although calculated fields usually deal with numeric information, they don’t always. You have genuinely useful ways to manipulate text as well.

If you have text information, then you obviously can’t use addition, subtraction, and other mathematical operations. However, you can join text together. You can, for instance, link several fields of address information together and show them all in one field, conserving space (and possibly making it easier to export the information to another program).

To join text, you use the ampersand (&) operator. For example, here’s how to create a FullName field that draws information from the FirstName and LastName fields:

	FullName: [FirstName] & [LastName]

This expression looks reasonable enough, but it’s actually got a flaw. Since you haven’t added any spaces, the first and last name end up crammed together, like this: BenJenks. A better approach is to join together three pieces of text: the first name, a space, and the last name. Here’s the revised expression:

	FullName: [FirstName] & " " & [LastName]

This produces values like Ben Jenks. You can also swap the order and add a comma, if you prefer to have the last name first (like Jenks, Ben) for better sorting:

	FullName: [LastName] & ", " & [FirstName]

You can even use the ampersand to tack text alongside numeric values. If you want the slightly useless text “The price is” to appear before each price value, use this calculated field:

	Price: "The price is: " & [Price]