Section 6.2. Creating Queries | Access 2007[c] The Missing Manual

6.2. Creating Queries

Access gives you three ways to create a query:

The Query wizard gives you a quick-and-dirty way to build a simple query. However, this option also gives you the least control.

Note: If you decide to use the Query wizard to create your query, then you'll probably want to refine your query later on using Design view.
Design view offers the most common approach to query building. It provides a handy graphical tool that you can use to perfect any query.
SQL view gives you a behind-the-scenes look at the actual query command , which is a piece text ( ranging from one line to more than a dozen ) that tells Access exactly what to do. The SQL view's where many Access experts hang outand though it seems intimidating at first glance, it's actually not that difficult to decipher (as you'll see in Section 6.2.3).

6.2.1. Creating a Query in Design View

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 resulta query that gets the results that fall in the first quarter of 2007is shown in Figure 6-6.

Here's what you need to do:

Choose Create Other Query Design .

A new design window appears, where you can craft your query. But before you get started, Access pops open the Show Table dialog box, where you can choose the tables that you want to work with (Figure 6-1).
Select the table that has the data you want, and then click Add (or just double-click the table ).

In the Boutique Fudge example, you need the Orders table.

Access adds a box that represents the table to the design window. You can repeat this step to add several related tables, but for now stick with just one.
Click Close .

The Show Table dialog disappears, giving you access to the Design view for the query.

Select the fields you want to include in your query .

To select a field, double-click it in the table box (Figure 6-2). Take care not to add the same field more than once, or that column shows up twice in the results. If you're using the Boutique Fudge example, then make sure you choose at least the ID, DatePlaced, and CustomerID fields.

Figure 6-1. You've seen the Show Table dialog box beforeit's the same way you added tables to the relationships window in Chapter 5.

Figure 6-2. Each time you double-click a field in the table box, Access adds it to the field list at the bottom of the window. You can then configure various settings to control filtering criteria and sorting for that column. If you don't want to keep mousing back to the table box, then you can add a field directly to the column list by choosing its name from the dropdown Field box.

You can double-click the asterisk (*) to choose to include all the columns from a table. However, in most cases, it's better to add each column separately. Not only does this help you more easily see at a glance what's in your query, it also lets you choose the column order, and use the field for sorting and filtering.

Note: A good query includes only the fields you absolutely need. Keeping your query lean ensures it's easier to focus on the important information (and easier to fit your printout on a page).

Arrange the fields from left to right in the order you want them to appear in the query results .

When you run the query, the columns appear in the same order as they're listed in the column list in Design view. (Ordinarily, this system means the columns appear from left to right in the order you added them.) If you want to change the order, then all you need to do is drag (as shown in Figure 6-3).

Figure 6-3. To reorder your columns, drag the gray bar at the top of the column you want to move to its new home. This technique's similar to the technique you use to arrange columns in the datasheet (Section 3.1.2). In this example, the DatePlaced field's being moved to the far left side.

If you want to hide one or more columns, then clear the Show checkbox for those columns .

Ordinarily, Access shows every column you've added to the column list. However, in some situations you want to work with a column in your query, but not actually display its data. Usually, it's because you want to use the column values for sorting or filtering.

Choose a sort order .

If you don't supply a sort order, then you'll get the records right from the database in whatever order they happen to be. This convention usually (but not always) means the oldest records appear first, at the top of the table. To sort your table explicitly, choose the field you want to use to sort the results, and then, in the corresponding Sort box, choose a sorting option. In the current example, the table's sorted by date in descending order, so that the most recent orders are first in the list (Figure 6-4).

Figure 6-4. Choose Ascending if you want to sort a text field from AZ, a numeric field from lowest to highest, or a date field from oldest to most recent. Choose Descending to use the reverse order. Section 3.2 has more information about sorting and how it applies to different data types.

Tip: You can sort based on several fields. The only trick's that your columns need to be ordered so that the first sorting criteria appears first (to the left) in the column list. Use the column rearranging trick from step 5 to make sure you've got it right.

Set your filtering criteria .

Filtering (Section 3.2.2) is a tool that lets you focus on the records that interest you and ignore all the rest. Filtering cuts a large swath of data down to the information you need, and it's the heart of many a query. (You'll learn much more about building a filter expression in the next section.)

Once you have the filter expression you need, place it into the Criteria box for the appropriate field (Figure 6-5). In the current example, you can put this filter expression in the Criteria box for the DatePlaced field to get the orders placed in the first three months of the year:

 >=#1/1/2007# And <=#3/31/2007#

You aren't limited to a single filterin fact, you can add a separate filter expression to each field. If you want to use a field for filtering but not display it in the results, then clear the Show checkbox for that field.

Figure 6-5. Here's a filter that finds orders made in a date range (from January 1 to March 31, in the year 2007). Notice that when you use an actual hard-coded date as part of a condition (like January 1, 2007 in this example), you need to bracket the date with the # symbols. For a refresher about date syntax, refer to Section 4.3.2.2.

Choose Query Tools Design Results Run .

Now that you've finished the query, you're ready to put it into action. When you run the query, you'll see the results presented in a datasheet (complete with lookups on linked fields), just like when you edit a table. (Figure 6-6 shows the result of the query on the Orders table).

You can switch back to Design view by right-clicking the tab title and then choosing Design View.

Figure 6-6. Here are the results of a query that shows orders placed within a specific date range. You can use the datasheet window to review or print your results, or you can edit information just as you would in a table datasheet.

Note: The datasheet for your query acquires any formatting you applied to the datasheet of the underlying table. If you applied a hot-pink background and cursive font to the datasheet for the Orders table, then the same settings apply to any queries that use the Orders table. However, you can change the datasheet formatting for your query just as you would with a table.

Save the query .

You can save your query at any time using the keyboard shortcut Ctrl+S. If you don't, then Access automatically saves your query when you close the query tab (or your entire database). Of course, you don't need to save your query. Sometimes you might create a query for a specific, one-time-only task. If you don't plan to reuse the query, then there's no point in cluttering up your database with extra objects.

The first time you save your query, Access asks for a name. Use the same naming rules that you follow for tablesrefrain from using spaces or special characters , and capitalize the first letter in each word. A good query describes the view of data that it presents . One good choice for the example shown in Figure 6-6 is FirstQuarterOrders_2007.

Note: Remember, when you save a query, you aren't saving the query results you're just saving the query design , with all its settings. That way, you can run the query any time to get the live results that match your criteria.

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.

Note: Remember, a query's a view of some of the data in your table. When you edit your query results, Access changes the data in the underlying table. On the other hand, it's perfectly safe to rename, modify, and delete queriesafter all, they're there to make your life simpler.

6.2.1.1. Building filter expressions

The secret to a good query's getting the information you want, and nothing more. To tell Access what records it should get (and which ones it should ignore), you need a filter expression .

The filter expression defines the records you're interested in. If you want to find all the orders that were placed by a customer with the ID 1032, you could use this filter expression:

 =1032

To put this filter expression into action, you need to put it in the Criteria box under the CustomerID field.

Technically, you could just write 1032 instead of =1032 , but it's better to stick to the second form, because that's the pattern you'll use for more advanced filter expressions. It starts with the operator (in this case, the equals sign) that defines how Access should compare the information, followed by the value (in this case, 1032) you want to use to make the comparison.

Note: If you're using a multivalue field (Section 5.3.2.2), then Access includes the record in the query results if any value matches your filter. Imagine a Classes table that includes a multivalue InstructorID field (indicating that more than one teacher can team up to teach the same class). If you write the filter condition =1032 for the InstructorID field, then Access includes any record where instructor 1032 teaches, whether or not other teachers are also assigned to the class.

If you're matching text, then you need to include quotation marks around your value. Otherwise, Access wonders where the text starts and stops.

 ="Harrington Red"

Instead of using an exact match, you can use a range. Add this filter expression to the OrderTotal field to find all the orders worth between $10 and $50:

 <50 And >10

This condition's actually two conditions (less than 50 and greater than 10), which are yoked together by the powerful And keyword (Section 4.3.2.4). Alternatively, you can use the Or keyword if you want to see results that meet any one of the conditions you've included (Section 4.3.2.4). In Chapter 7, you'll consider some more powerful tools for crafting filter expressions.

WORD TO THE WISE
Don't Get Confused by Lookups

As you know, lookups change the way values appear on the datasheet. If you add a lookup on the CustomerID field in the Orders table, then you don't see a cryptic number like 1032. Instead, you see some descriptive information, like the name Hancock, John .

However, when you write your filter expression, you need to remember what information's actually stored in the field. So the CustomerID filter expression =1032 works fine, but =Hancock, John doesn't, because the name information's actually stored separately. (It's in the Customers table, not the Orders table.)

Sometimes, you really do want to create a filter expression that uses linked information. You may want to find records in the Orders table using a customer name instead of a customer ID, because you don't have the ID value handy. In this situation, you have two choices:

You can look up the ID value you need in the Customers table before you start. Then, you can use that value when you build your query for the Orders table.
You can use a join query to get the name information from the Customers table, and display it along-side the rest of your order details. You'll learn how to perform this trick in Section 6.3.

Date expressions are particularly useful. Just remember to bracket any hardcoded dates with the # character (Section 4.3.2.2). If you add this filter condition to the DatePlaced field, then it finds all the orders that were placed in 2007:

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

This expression works by requiring that dates are earlier than January 1, 2008, but later than December 31, 2006.

Tip: With a little more work, you could craft a filter expression that gets the orders from the first three months of the current year, no matter what year it is. This trick requires the use of the functions Access provides for dates. You'll see how to use them in Section 4.1.3.

6.2.1.2. 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:

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

6.2.2. Creating a Simple Query with the Query Wizard

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:

POWER USERS' CLINIC
How Indexes Speed Up Searches

In Chapter 2 (Section 4.1.3.1), you learned about table indexes, and how to create them. (An index is a list of all the values in one field, in sorted order. Next to each value is a pointer to the full record in the table.)

Indexes have two purposes. First, they can prevent duplicate values (Section 4.1.3). Second, they can help Access perform searches more speedily. Access can often hunt through an index faster than it can scan an entire table. Not only is the index physically smaller (because it has the value from only one field), but it's also sorted, so Access can skip to the right place more quickly.

To understand the difference, suppose you ask Access to find the "Bavarian Tart" record in the Products table. If you have an index for the ProductName field, then Access can scan the B section until it finds the right entry, and then jump to the full details. If you don't have an index, Access is forced to search the entire table, record by record. The table isn't in any sorted order, so there's no telling how long it'll take before Access stumbles across the right record.

At first glance, indexes sound tremendously useful, and you'll be tempted to create them for all your table fields. But indexes have drawbacks. The more indexes you add, the more work Access needs to do when inserting and updating records. Each index also takes up some space. In fact, indexes are a waste of resources unless you know they'll improve search performance.

Here are some reasons to consider using an index to improve performance:

Your database is huge . If you have only a few hundred records, Access can almost always scan the entire table faster than it can use an index, due to the way that hard drives work. Even if you have thousands of records, Access can often load the whole shebang into your computer's memory, so it doesn't have to wait for the hard drive to respond, and all your queries are blisteringly fast.
Your search is slow . There's no reason to enhance a query if it's already working at top speed. Most Access fans can search giant databases day after day without ever having to wait.
The field you want to index is used in a search . Don't index a field unless you're using it in a filter condition. If you often search for a single, specific customer by looking up the last name, then consider adding an index on the LastName field.
The field you want to index is unique (or close to it) . It makes sense to add an index to the ProductName field in the Products table, because very few products (if any) share the same name. On the other hand, it doesn't make sense to index the City field in the Customers table, because many customers live in the same city. As a result, an index on the City field would be inefficient, and Access probably wouldn't bother to use it at all.

Choose Create Other Query Wizard .

Access gives you a choice of several different wizards (Figure 6-10).
Choose a query type. The Simple Query wizard's the best starting point for now

The Query wizard includes a few common kinds of queries. With the exception of the crosstab query, there's nothing really unique about any of these choices. You'll learn to create them all using Design view:
- Simple Query Wizard gets you started with an ordinary query, which displays a subset of data from a table. This query's the kind you created in the previous section.
  
  Figure 6-10. In the first step of the Query wizard, you choose from a small set of basic query types.
- Crosstab Query Wizard generates a crosstab query, which lets you summarize large amounts of data using different calculations. You'll build one of your own in Section 9.1.
- Find Duplicates Query Wizard is similar to the Simple Query wizard, except it adds a filter expression that shows only records that share duplicated values. If you forgot to set a primary key or create a unique index for your table (Section 4.1.3), then this can help you clean up the mess.
- Find Unmatched Query Wizard is similar to the Simple Query wizard, except it adds a filter expression that finds unlinked records in related tables. You could use this to find an order that isn't associated with any particular customer. You'll learn how this works in Section 6.3.2.1.
Click OK .

The first step of the Query wizard appears.
In the Tables/Queries box, choose the table that has the data you want. Then, add the fields you want to see in the query results, as shown in Figure 6-11 .

For the best control, add the fields one at a time. Add them in the order you want them to appear in the query results, from left to right.

You can add fields from more than one table. To do so, start by choosing one of the tables, add the fields you want, and then choose the second table and repeat the process. This process really makes sense only if the tables are related. You'll learn more in Section 6.3.

Click Next .

If your query includes a numeric field, the Query wizard gives you the choice of creating a summary query that arranges rows into groups, and calculates information like totals and averages. You'll learn about summary queries in Chapter 7 (Section 7.3). For now, if you get this choice, pick Detail and then click Next.

Figure 6-11. To add a field, select it in the Available Fields list, and then click the > arrow button (or just double-click it). You can add all fields at once by clicking the >> arrow button, and you can remove fields by selecting them in the Selected Fields list and then clicking <. In this example, three fields are included in the query.

The final step of the Query wizard appears (Figure 6-12).

Figure 6-12. In the last step, you choose the name for your query, and decide whether you want to see the results right away or refine it further in Design view.

Supply a query name in the "What title do you want for your query?" box .
If you want to fine-tune your query, then choose "Modify the query design". If you're happy with what you've got, then choose "Open the query to view information" to run the query .

One reason you may want to open your query in Design view is to add filter conditions (Section 3.2.2) to pick out specific rows. Unfortunately, you can't set filter conditions in the Query wizard.
Click Finish .

Your query opens in Design view or Datasheet view, depending on the choice you made in step 7. You can run it by choosing Query Tools Design Results Run.

GEM IN THE ROUGH
Queries on Queries

The examples in this chapter assume you're creating a query based on a table in your database. But keen eyes may have spotted a different choicenamely, you can create a query that selects results from another query. If you're creating a query in the design window, then you simply need to use the Queries tab of the Show Table dialog box (instead of the Tables tab). If you're creating a query with the wizard, then all your queries appear in the Tables/Queries list in the first step, along with your tables.

You most often build a query on another query when you want to reuse your hard work and simplify complex queries. For example, imagine you want to create a query for Boutique Fudge that gets the customers who've placed an order in the last month, and retrieves all their customer information. Based on that query, you may want to build a more specialized summary query (Section 7.3) that arranges the customers into groups based on their city, and counts how many recent purchasers you have in each location. You could create a single query that does both these steps. But by splitting this logic into two pieces, you get the ability to easily reuse the first query (recent customers) to create many more related queries.

6.2.3. Understanding the SQL View

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.

Note: Database gurus still argue about whether SQL is pronounced Es-Cue-El (which is historically correct) or Sequel (which is how it's used in the product name Microsoft SQL Sever). In this book, we assume you'll use the more hip Sequel .

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:

You want to perform an action that's supported by SQL but isn't available in the query designer. Of course, you'll need to know more than a little about SQL to edit your command. Later in this chapter (Section 6.2.3.2), you'll see how to use SQL view to create a union query that combines the results from two similar tables.
You want to learn SQL. This ambition 's a great idea if you're planning a career as a database administrator, but it's not really necessary if you're sticking with Access.
You want to transplant a command to another database product. You could be in the process of moving databases from Access to a high- powered Oracle database. This job's ambitious, and you'll find that while you can move your data to its new home, you can't move other database objects like queries. Instead, you need to take a closer look at the underlying SQL, which you can use to reconstruct the query in the new database.
You're just plain curious . Looking at the SQL for your queries clears up a lot of the mystery behind how Access works.
You're a SQL coding genius, and the query designer just slows you down.

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.

6.2.3.1. Analyzing a query

Although SQL looks complex at first glance, all queries boil down to essentially the same ingredients . Consider the query for finding high-priced orders, which looks like this (with each line numbered for easy reference):

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

Here's a breakdown of the first two lines:

Line 1 starts with the word SELECT, which indicates it's a query that selects records (like all the queries you've seen in this chapter).

After the word SELECT is a comma-separated list of fields that you want to see. Each field's written out in the long format TableName.FieldName, just in case you decide to create a query that uses more than one table.
Line 2 starts with the word FROM, which indicates the table (or tables) that you're searching. In this case, the Products table has the records you need.

These two lines represent a complete functioning query. However, you'll often have more lines that apply filtering settings and sorting:

Line 3 starts with the word WHERE, which indicates the start of your filter conditions. In this case, there's only onea requirement that the product price be over $50. If you've defined more than one criteria on different fields, then you see them all here, joined together using the AND operator.

Note: Access goes a little crazy with parentheses in the filter conditions. You could rewrite WHERE (((Products.Price)>50)) more simply as WHERE Products.Price>50 . Access uses the parentheses because they make it easier to sort out complex queries with multiple conditions.
Line 4 starts with the words ORDER BY, which defines the sorting order. In this case, records are sorted from lowest to highest using the value in the Price field. In the case of a descending sort, you'd see the abbreviation DESC after the field name. If you're sorting on multiple fields, then you see a comma-separated field list.

The command ends with a final semicolon (;). Access doesn't need this detail, but it's a SQL world convention.

The lesson here's that every query you build is shaped out of a few common ingredients, represented by the SELECT, FROM, WHERE, and ORDER BY sections.

Access keeps all the different views of a query synchronized. If you make a change to the SQL text, and then switch back to the Design view, then you see the newly modified version of the query (unless you've made a mistake, in which case Access delivers an error message).

To try this characteristic out, you can modify the SQL text so that it selects an extra column and sorts on two fields, so that products with the same price are arranged alphabetically (the new parts are highlighted in bold):

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

Right-click the tab title, and then choose Design View to see how these changes appear in the query designer.

6.2.3.2. 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 tablesin this case, the FirstName and LastName fieldsmatch up.

Note: You can create a union query even if the column names differif 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 dataunion 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 thatremarkablyit 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:

Choose Database Tools Show/Hide Object Dependencies. The Object Dependencies box appears on the Access windows right side. (Choose the same command again when you want to hide it.)
In the navigation pane, select the database object that you want to examine.
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).
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.