13.3 Querying a Database

The Purpose of Queries in Databases

Queries are an essential part of making a database accessible. Imagine trying to make sense of hundreds, thousands, or even more records without a tool to help you. It is simply not possible. The more complex your dataset, the more important it is to use queries and reports to retrieve data. Most databases have too many records to work with unless you have well-written queries. A well-written query is specific, organized, and comprehensive, meaning that it contains everything you need.

You are most likely to use queries to retrieve a specific record or set of records, but queries have other functions as well. For example, they can summarize data and perform calculations. You can even set up queries that show you the most current data in the database each time you refresh your records.

Designing Queries

Before building a query, you need to take some time to think through how the query will work and what it will accomplish. Start with a question, such as one of the following examples:

• What does inventory look like right now?
• What sales were made last week?
• What were the top products sold, based on volume, last quarter?

These questions demonstrate in detail how to structure the query. Once we have the question in mind, we can then consider other items that inform our query design: what data is available, where it is located, how it may need to be processed (e.g., sorted, filtered, or calculated) to get to the answer, and how the output should be organized (e.g., sorted by date).

This design, or model, tells you how to create the query in your software. Once you have created the query in Access, you will need to test it by running it a few times to make sure it returns the values you are looking for. You will likely revise your queries as you test them. Remember that the more complex a query is, the more revision and testing you will need to do.

A query based on your question or need for information contains specific components. In Access, these are as follows:

• The name of the query (if you will be saving it), which appears at the top of the query screen
• The field in which your data is stored
• The table in which the field is located
• Sort, which determines the column that will be used to sort the query (e.g., in numerical order by product ID)
• Criteria (text that can be used to limit the results of a query)
• Or, which allows you to create more complex statements

When you create a query in Access through the Query Wizard (Figure 13.12), these areas will be filled in by the wizard. If you need to modify a query or want to create one without using the wizard, you will use the query grid to set up the query. You learn more about this later in the section on Editing Queries with the QBE Grid and in the chapter on Advanced Database Use.

Figure 13.12 The Query Wizard guides users through the process of creating database queries step by step. It provides a graphical interface to help users select tables, fields, criteria, and sort options, making it easier to generate complex queries without writing code manually. (Used with permission from Microsoft)

Using these areas of the query allows you to accomplish multiple tasks, including choosing the data you want to include, omitting data that is not of interest to you, sorting data, and even combining data from multiple tables.

Running a Simple Query

A simple query is one that finds and displays information. Very simple queries may show all the data in the database, in columns, or all the information in one specific field or column. Once a query has been created and saved, you can run the query whenever it is needed to return the updated results. Each saved query has a name associated with it, and typically these names follow a specific pattern that helps you to differentiate between them. For example, a version of a customer list sorted by last name and the same list sorted by state would be named qryCustomerLastNameState.

In the Access database with queries, you will see a list of saved queries in the navigation section of your database. For example, in the database shown in Figure 13.13, you can see four queries under the Queries heading: ProductCategoryList, ProductList with client information, ProductsACTIVE, and ProductsALL. These simple queries are already created; to run them, simply double-click on the name of the query you want.

Figure 13.13 Use Saved Queries to prevent repetitive query design in Access. Here, the saved query ProductList with client information is selected. (Used with permission from Microsoft)

Once you double-click on a query name, it will open and show you the results table. The query will generate updated results every time it is opened, allowing users to use a simple query over and over, even as data is refreshed in your database.

When queries—or any database objects—are open, you will see the name of the object on a tab above your work area. To close a query, click on the X to the right of the object’s name.

Switching Views

Access can switch between the Design View of the query and the results of the query. For queries, you will frequently change views to move back and forth between the underlying query structure and the results of running the query. When you look at the structure in Layout View, you can modify how the query works. When you go to SQL View, you are also able to modify the query structure (see Figure 13.14).

Figure 13.14 Viewing a query in SQL View allows you to see and edit the underlying SQL code that defines the query’s structure and criteria. (Used with permission from Microsoft)

To change views, first click on the View command on the ribbon, and then choose the view you would like to see. You can choose from three views:

• Datasheet View shows you the results of the query in table form.
• SQL View shows the query in structured query language form.
• Design View shows the query in the form of a QBE (query by example) grid.

Using these views, you can see your query in multiple ways, first visualizing the query and then seeing the results quickly.

Query Walkthrough

In today’s fast-paced business landscape, staying ahead of the competition requires a deep understanding of your products and their performance. WorldCorp’s state-of-the-art sales and marketing database is designed to maximize their sales potential. At the core of this powerful database lies the ability to perform queries that deliver invaluable insights. A query in WorldCorp’s sales and marketing database returns a list of products, including the product ID code, product category code, and notes (if any are present). Notice that there is only one table, called the Product List, and that all three fields are used in the query.

First, Figure 13.15 shows the query in Design View. Note that the list is sorted in ascending order based on the product ID. The result of the query is shown in Figure 13.16.

Figure 13.15 Using a query creates a list of all products found in a database. (Used with permission from Microsoft)

Figure 13.16 The result of the query will return everything in the database that fits the criteria you included in your query fields. (Used with permission from Microsoft)

Now, let’s look at a query that uses more than one table. In this example, the query returns a list of products with information about wholesalers who have showed interest in those products. Three tables are used:

• ProductList: list of products that WorldCorp offers
• WholesalerList: list of wholesalers that WorldCorp works with
• WholesalerInterestList: table that includes ProductID (from ProductList) and WholesalerID (from WholesalerList)

See that the tables are joined by the fields ProductID and WholesalerID. If you used only the WholesalerInterestList table, you would not be able to display the product category or the wholesaler name—information needed to make the results of the query useful for the sales and marketing team. If you look at the grid in Figure 13.17, you will also see that the table uses sorting and an expression (a combination of two fields displayed together). The results are shown in Figure 13.18.

Figure 13.17 A query across several tables can create a sorted list of products from the database by combining data from more than one table. (Used with permission from Microsoft)

Figure 13.18 The results will display only the columns that you chose to show in your query. (Used with permission from Microsoft)

Notice that this query combines several different techniques, including the use of multiple tables, sorting, criteria, and even an expression that combines fields in the query (in this case, the wholesaler ID and name). This is just a small demonstration of the power of queries.

Saving and Closing the Query

The first time you create a query, you need to give it a name. After that, if you modify the query, you will need to save it, and doing so will update the copy you have. To save the query, you can click on the Save icon on the quick access toolbar or use Ctrl+S on the keyboard. Access will also prompt you to save when you close a query that you modified.

If you do not plan on using a query again or if it is extremely straightforward, you may not want to save it, but if you are likely to reuse the query, you will want to give it a descriptive name. It makes sense to create a naming schema that you can continue. For example, if you are querying for a specific project team, you may start with the team name, like ProductTeam, to help you identify the query later on.

Editing Queries with the QBE Grid

Once a query is built, you can edit it using the QBE grid, which gives you the ability to look at a query and rearrange, modify, and add to it, as well as the ability to run the query and see the results.

To open a query in the QBE grid, right-click the query in the navigation area of the screen, or to open the query, click on the View command, and choose Design View, as Figure 13.22 shows.

Figure 13.22 The View command gives you a few different options for viewing a query. (Used with permission from Microsoft)

The QBE grid (Figure 13.23) allows you to interact with a query visually. It places the elements of the query on screen in order from left to right and gives you the ability to modify the format of the query. You can do the following using the QBE grid:

• View, add, or change the tables used in the query
• Add or reorder fields
• Sort values returned by the field
• Choose whether a field is shown in the query (e.g., you may want to sort or use a field but not have it appear in the results)
• Add criteria to limit the records returned by the query

Figure 13.23 Use the QBE grid to interact with or modify a query. (Used with permission from Microsoft)

Building a Query with the QBE Grid

Although the Query Wizard is a useful tool and a good way to quickly start a query, using the QBE grid gives you more options for building complex queries. The QBE grid consists of a table that makes commands available.

In setting up the query, follow these steps:

1. Add tables.
2. Choose fields, one per column header.
3. Choose the sorting criteria.
4. Run the criteria to see results.
5. Return to Layout View to continue working.

To set a sort order, which will control the order of records displayed in the query, follow these steps:

1. View the query in Design View.
2. Locate the column you want to sort.
3. Click on the down arrow in the Sort row and choose the sort order (ascending, descending, or not sorted). By default, the column is not sorted.

You can also set criteria here to control which records are displayed. Criteria are commands that are written using symbols together with a text string that tells Access what you want the query to do. A criterion statement has two parts: the criterion/operator and descriptive text. For example, in the criterion statement

= 100

the equals sign serves as the operator, and 100 is the descriptor.

There are several different criteria you can use within your queries. Some of the operations (criteria) are displayed in Table 13.4.

Databases can hold hundreds, thousands, and even hundreds of thousands of records. One way to limit the results to what is relevant to your project or to the decision you need to make is to use criteria to limit the data that a query returns. Limiting the results makes it easier for you to work with the data and helps ensure that your decisions are both accurate and effective.

Setting criteria can help you select specific items in an Access database by showing only items that match the criteria you have set in creating the query. Individual criteria can be used in expressions, or statements, that specify values to select or omit.

Joining Tables

Splitting data into tables in a relational database like Access enhances database performance, which is a key issue in working with very large datasets. It also increases flexibility by allowing us to chunk, or separate, our data into smaller sets, which are easier to work with. Splitting up the data also enables us to avoid creating many null (empty) fields. (Imagine using a single spreadsheet to manage all your information—it would not be useful, as it would have too many fields, and many of those fields would be blank.)

When using a relational database to separate data into smaller, ID-focused tables (e.g., a table including only salespersons, another for clients), you need to join these tables based on key fields. In this example, you would likely create a primary key, SalesRepID, on the sales rep table, and then include this same field as a foreign key on the client table. You can then create queries that will join these tables by making sure that Access understands the connection between the fields.

In fact, the ability to create and define relationships between tables is one of the most important functions of a relational database. If you consider a database a set of data tables with relationships between them, Access needs to be able to connect related fields between one table and another. It does so by means of the connection or relationship known as a join.

A primary key, or unique key, is a field that uniquely identifies a record. As with an ID number, no two records can have the same primary key. A foreign key field is a primary key field that appears in another table. We can join foreign keys to primary keys to create a relationship between the two fields.

Recall that a join refers to the relationship between two fields in different tables. Access will create a relationship or join when it sees a primary key field in one table and the same primary key used in another. This is because Access understands that these fields have a relationship.

Joins appear between two fields in two or more tables in the query design screen. They are shown as lines connecting the fields, which represent the relationship between the two tables as a one-to-one, one-to-many, or many-to-many join. Access will create a join automatically when it recognizes the same field in two tables. At times, however, Access will not recognize the relationship, or you may want to relate two fields for the purpose of a specific query. In these instances—when we realize that two fields are related but that Access does not automatically understand this—we can also create joins manually. For example, this happens when different field names apply to the same field: A person may realize that CustomerNumber and CustomerID are the same, but Access likely would not recognize this. In this case, it is our job to add the relationship, letting Access know that these fields should be joined when we perform a query, as Figure 13.25 and Figure 13.26 show.

Figure 13.25 Using joins in a query allows you to establish connections between fields in different database tables, enabling you to retrieve and display related data by specifying how the tables are linked based on shared values or keys. (Used with permission from Microsoft)

Figure 13.26 The Join Properties dialog box enables users to specify the type and criteria for joining tables in a database query, defining how records from multiple tables are related and included in the query results. (Used with permission from Microsoft)

Types of Joins

There are two main types of joins: inner and outer. The type of join controls what data will display when the query runs. In an inner join, Access looks at the common fields and data between the two tables and only displays the matches, omitting any records from either table that do not match. The inner join is the most common type of join and is the default type for Access.

In an outer join, Access looks at the common fields and displays the related (matched) data plus the rest of the records from a single specified table. You can use the Join Properties box to choose which table will have all its records displayed.

As an example of an inner join, the sales and marketing database has a table with client information and a table with sales invoices. Both tables include the field ClientID. These tables are related using the default join type, the inner join.

Suppose the team at WorldCorp needs to see a list of all products with the clients that have purchased them so they can share new sales information appropriately. The tables in Figure 13.27 include the fields ClientInfo and SalesInvoices.

Figure 13.27 Access uses lines between tables to denote that a relationship exists between them. (Used with permission from Microsoft)

After looking at what the team actually needs for the process, you decide to add the fields from ClientInfo, including ClientID, ClientName, and ClientMailingAddressAll, and to use two SalesInvoices table fields—ClientID and ProductID—to narrow the results to just the clients who have purchased specific products.

After creating a new query and adding both tables to the grid, you realize that Access did not automatically create the join. You drag ClientID from the Client table to the matching ClientID in the SalesInvoice table. This creates an inner join, which will return only the details for the records that match.

You then add the fields for the query into the query grid. Add the ProductID field first because it should appear first on the screen. Then add the ClientID field, followed by ClientName and ClientMailingAddressAll.

Using these fields, as Figure 13.28 shows, you created a query that returns a list of each product, followed by the clients that purchased it. By adding ClientID, ClientName, and ClientMailingAddressAll, the team can send the correct mailings to the customers that have purchased these specific products in the past.

Figure 13.28 A query can use joins to return lists with data from multiple tables. (Used with permission from Microsoft)

Creating Joins

To create a join in Access, you need to have both tables open on the query design screen. With the tables open, click on the field you want to join in one table and drag it to the matching field on the other table. Access will usually create an inner join, displaying only the records that match on both tables.

If you want to display the data differently, you can double-click the join line and change the type of join. To change a join, double-click on the join itself in the query grid. The Join Properties box (Figure 13.29) will open. In the box, you can change the tables or columns of the join and can also change the type of join relationship. Sometimes it is helpful to modify the join on a dataset to see the results. The Join Properties box is helpful because it gives you a quick reminder of the results you can expect from the join, using the names of your tables.

Figure 13.29 You can change a join in the Join Properties box. (Used with permission from Microsoft)

If you decide you do not want or need a join for any reason, you can click on the join line in the query layout view and click Del on your keyboard. The join will be removed.