How to Create Simple Queries

Access queries allow you to ask questions about your databaseand, even better, to get the answers back. More generally , you might think of queries as a way to request particular bits of information from your database. Here are some examples of the types of information you can get from a query:

• What are all the plants in the database, sorted in alphabetical order?

• Which medicinal plants are in the database?

• Which catalogs have I purchased plants from?

• How many plants have I purchased from each catalog?

As you might guess, some of these queries are more complex than others. In this chapter, you'll learn how to construct simple queries, starting with using the aptly named Simple Query Wizard. You'll learn about more complex queries in Chapter 12, "Getting Down to Business with Queries."

Note

You can download the Chapter 7.mdb sample file, which is inclusive of all the examples in this chapter, from http://www.quepublishing.com/. If you want to follow along with our examples, download Chapter 6.mdb .

Using the Simple Query Wizard

You've already seen some of the helpful wizards Access supplies to guide you through tasks as you get started with a database. Queries are no exception to this rule: The easiest way to get started with queries is by using the Simple Query Wizard. Here's how:

1. Launch Access and load the Plants database.

2. Select the Queries shortcut in the Database window.

3. Double-click the shortcut icon labeled Create Query By Using Wizard . This opens the Simple Query Wizard, as shown in Figure 7.1.

   Figure 7.1. The Simple Query Wizard.

   

4. The drop-down list under the diagram lists all the tables and queries in your database. Right now you don't have any queries, so it shows only the three tables in the database. Select the Plants table from the list. You'll see that the Available Fields list changes to show the fields that are in the Plants table.

5. Select the CommonName field in the Available Fields list and click the > button to move it to the Selected Fields list.

6. Double-click the LatinName field in the Available Fields list. This also moves the field to the Selected Fields list.

7. Click the Next button to move to the second panel of the wizard.

8. Type the name PlantNames as the title for your new query. Select the Open the Query to View Information radio button, as shown in Figure 7.2.

   Figure 7.2. Completing the Simple Query Wizard.

   

9. Click the Finish button to create the query and open it in datasheet view, as shown in Figure 7.3.

   Figure 7.3. A new query open in datasheet view.

   

Although the query datasheet looks very similar to a table datasheet, there are some important differences:

• A query doesn't show exactly the data from a table In this particular case, the query shows only two fields from the underlying table, even though you know that a lot more information exists in the table. As you'll see throughout this chapter, a query does not have to show all the rows from a table either.

• The query doesn't actually contain any data The data it displays comes from the table, and any changes you make to the data are written back to the table.

Later in the "Working with the Query Datasheet" section in this chapter, you'll learn more about working with data in a query datasheet. For now, just click the Close button to close the datasheet.

Caution

Keep in mind that if you delete data from a query, you're deleting it from the tables behind the query as well. Be sure you don't accidentally delete important data.

Building a Query on a Query

The PlantNames query is based directly on the Plants table. But you can also build a query that draws its data from another query. Here's an example:

1. Launch the Simple Query Wizard from the Queries tab of the Database window.

2. In the Tables/Queries drop-down list, select the PlantNames query. You'll see that only the fields in the query are listed in the Available Fields list box.

3. Double-click the CommonName field to move it to the SelectedFields list box.

4. Click Next.

5. Name the new query PlantCommonNames .

6. Click Finish , and Access creates the new query and opens its datasheet, which is shown in Figure 7.4.

   Figure 7.4. A query based on a query.

   

If you want, you can base a query on a query that's based on a query, and so on. You can stack them up as high as you want. No matter how many queries are in the list, though, at the bottom level the data is always stored in the tables, and any edits you make (usually) flow back to the tables.

Using More Than One Table

You might have noticed the instructions at the top of the Simple Query Wizard that state "You can choose from more than one table or query." Here's how this aspect of the Simple Query Wizard works:

1. Launch the Simple Query Wizard from the Queries tab of the Database window.

2. Select the Plants table from the Tables/Queries drop-down list.

3. Move the CommonName and LatinName fields from the Available Fields list to the Selected Fields list.

4. Select the Types table from the Tables/Queries drop-down list. Note that the fields you've already selected remain in the Selected Fields list, but the Available Fields list now shows the fields from the Types table (see Figure 7.5).

   Figure 7.5. Creating a query with fields from more than one table.

   

5. Move the Description field from the Available Fields list to the Selected Fields list.

6. Click Next.

7. Name the new query PlantsWithTypes and click Finish . Figure 7.6 shows the datasheet view of this query.

   Figure 7.6. A query with fields from more than one table.

   

Look at all that repeating data in the Description field! You learned in Chapter 6, "Tapping the Power of Relationships," that repeating data is a bad thing, so what's going on here?

This is one of the areas where you need to carefully distinguish between tables and queries. Repeating data in tables is indeed a bad thing; that's why you split repeating data off into separate tables when you're designing a database. By storing each piece of data in a single location, you can make your database more efficient and help prevent data entry errors.

But you'll recall that storing data in relational tables does have disadvantages. The result is that the data in a table can be cryptic to human beings. Recall the difficulties in entering a TypeID for plants before you set up the lookup field.

Queries work together with tables to give you the best of both worlds . By using a query, you can store your data in an efficient, relational form while still presenting it to human beings in a more natural form, complete with repeating data if it makes sense to look at things that way. The data in a query is calculated at runtime by referring to the underlying tables; it's never stored, so there's no violation of the rule to store data in only one place.

Tip

Design your tables to store data efficiently ; then design your queries to present the data any way you want.

Working with the Query Datasheet

Query datasheets not only look the same as table datasheets, but also behave the same way. All the keystroke shortcuts you learned in Chapter 5, "Building Your First Tables," apply equally well to query datasheets. You can also edit data in query datasheets, just as you can in table datasheets . Here's how:

1. Double-click the PlantNames query in the Database window to open it in datasheet view.

2. Click in the last cell in the CommonName column of the datasheet, where the data is currently Yarrow .

3. Change the value to Common Yarrow , as shown in Figure 7.7. Note the little pencil icon that appears to the left of the row while you do this. It's an indication that you've made changes that are not yet saved.

   Figure 7.7. Editing data in a query datasheet.

   

4. Click in any other row of the datasheet to save your change to the database.

5. Close the datasheet.

6. Select the Tables shortcut of the Database window.

7. Right-click the Plants table and select Open . You'll see that your new data appears in the table, even though you made your edit in the query.

8. Click in the Common Yarrow name and change it back to just Yarrow .

9. Close the table datasheet.

If you had any doubts that you're really editing table data when you work with a query datasheet, that set of steps should remove them.

But what happens when a query contains data from two different tables? The answer is that you can still edit the data, although sometimes the results might not be what you expect. Try these steps for an example:

1. Click the PlantsWithTypes query in the Database window. Then click the Open button on the Database Window toolbar.

2. Click in the cell containing the text Black-Eyed Susan and change it to Blue-Eyed Susan .

3. Press the Tab key twice to move the highlight to the last column of the first row of the datasheet, which currently reads Decorative.

4. Type Ornamental over Decorative .

5. Press Tab again. Your datasheet now appears similar to Figure 7.8. Note that Decorative has been replaced with Ornamental everywhere that it appears.

   Figure 7.8. Editing data in a query datasheet with two tables.

   

6. Click back into the original row and reverse your changes, so that the data is back to its original values.

Possibly you found the results of step 5, where four rows changed at once, rather surprising. But if you think about how the query works, you should be able to tell what's going on. The query draws its data from both the Plants table and the Types table, and each row of the query corresponds to one row in the Plants table. The CommonName and LatinName each come directly from the Plants table.

But what about the Description? That comes from the Types table. When the query needs to display the Description field, it first retrieves the TypeID value from the Plants table. That value tells the query which row of the Types table should be matched to the current row of the Plants table.

So, even though Decorative appears four times in the original datasheet, it's stored only once in the database. The query looks up the value for each row and thus displays it four times.

Now, what happens when you change the value? The query knows which row in the Types table it used to retrieve the original value, and it updates that row in the table. But then it reconsiders all the other rows, and it updates the value to the new value everywhere that it used that row. The net effect is that changing one row of data can affect many rows in a query that displays data from multiple tables. This feature of Access is called row fixup , and it prevents you from seeing data in a query that no longer exists in the underlying tables.

Note

Access uses the information you entered when you created relationships to decide which field to use to look up data from a related table. So, define your relationships before you build your queries!