Databasics VI: Exploring query types

In the previous tutorial we used Query By Example (QBE) on a database of countries to answer questions such as ‘Which countries are members of the UN Security Council?’ and ‘Which countries have a population over 20 million and an area over 2 million square kilometres?’ These are simple queries which we created using QBE and basic operators such as > (greater than).

It’s possible to ask more complex questions, such as:

• Which country joined the UN most recently?
• Which countries are in the top 5% in terms of area?
• Which five countries have the lowest population density?

The way you tackle these queries depends on which database program you use. I’ll be using Microsoft Access in the examples, as it has querying tools that leave most other PC databases in the shade. You can download a sample copy of the database to try this out for yourself.

Comparative queries

The simple queries we explored last time did nothing more than find a matching value (Name=Namibia), or compare a value in a field to a constant (Population<=20000000).

We can take this a step further and run queries that will compare one piece of data against the rest of the data in the table. These are real bread-and-butter queries. For instance, in a sales database, you might want the answers to questions such as: Which region had the lowest sales for the quarter? Who are our five best salespeople? Which salespeople performed better this quarter than last?

To ask these types of questions, you need to use something a little more sophisticated than QBE. In Access, you use the Query Designer.

Using Access’s Query Designer

Let’s start with the question, Which country joined the UN most recently? Here’s how to get the answer:

1. Open the Countries database in Access.
2. In the main Database Window, click Queries in the Objects list.
3. Double-click Create Query In Design View. A Query window opens together with a Show Table box containing all the tables in our database.
4. Select the Countries table, click Add, and then click Close. This adds the Countries database to the Query window, so we can work with it.
5. Click Name in the list of fields and then hold down the Ctrl key and click JoinedUN (you may need to scroll to see it). Drag the two fields to the left-hand column in the query design grid. The two fields will appear side by side in the grid. The top row in the grid shows the field name; the second row shows which table the field belongs to (we’ll get into multi-table queries at a later date); the third row lets you specify a sort order for the results; the fourth row (Show) specifies whether you want the contents of this field displayed in the results; the fifth and sixth rows specify criteria for selecting records.
6. In the JoinedUN column on the grid, click in the Sort row and, from the drop-down box, select Descending. This tells Access we want the table sorted with the most recent records (those with the’largest’ dates) first.
7. In the toolbar, beside the Sigma button you’ll see an empty drop-down box. This is the Top Values button. Type 1 in this box. We’re looking for the ‘top date’, so to speak – the most recent date in the JoinedUN field – so we want the top, single value.
8. Run the query by clicking the Run button (it has an exclamation mark on it). You’ll see the result is Tuvalu, which joined the UN on the 5th September, 2000.

geekgirl:geopolitical update

In fact, if you run this query on the updated version of the Countries database I now have online, you’ll find the answer is not Tuvalu, but South Sudan. East Timor, Switzerland, Montenegro and South Sudan have all joined the United Nations since I first wrote this article. While I’ve left the article text the same, I have updated the database to reflect these recent additions to the United Nations family (I haven’t had a chance to update the population figures, alas).

These changes show how useful database queries can be: Provided the data in your database is accurate and up to date, you can use a query you created years ago on your current data and get an accurate and up-to-date result.

Saving and re-running queries

Click the Close box on the Query window. You’ll be asked whether you want to save the query. Click Yes, name the query Most Recent Member and click OK.

Why should you save a query? After all, you already know the answer to your question is Tuvalu. But then, the answer won’t always be Tuvalu. Say Switzerland decides to join the UN this year, or a new country emerges from one of the bubbling spots on the globe and becomes a new member? (See the Geopolitical Update above.)

By saving the query, you can re-run it at any time and find the up-to-date answer to your question. This may seem a fairly trivial operation with our simple example, but when you’re creating complex queries or, more importantly, when your data changes regularly, saved queries eliminate a lot of work.

To use your saved query, simply double-click it in the Queries list.

Query types

The Most Recent Member query you created is called a Select query. Access lets you create five different types of query:

Select queries. Used to retrieve data from one or more tables and display the results in a datasheet, which you can save or modify. You can also use Select queries to group records and calculate sums, averages and so on.

Parameter queries. For creating on-the-fly queries which prompt the user for criteria at the time the query is run. For example, you can create a parameter query that answers the question: Which countries have a population greater than X and less than Y? Each time you run the query, it will prompt you for the values of X and Y. Thus you can use the same query repeatedly to discover different information.

Crosstab queries. Used to summarise data from one field and group it in tabular fashion according to two criteria.

Action queries. Queries that make changes to the records in a table. There are four type of action queries: Delete queries remove records from a table; Update queries make global changes to a group of records in a table; Append queries add records from one or more tables to the end or one or more tables; Make-table queries create a new table from all or part of the data in an existing table.

SQL queries. A query created using SQL, which is a highly advanced querying language. SQL queries give you enormous flexibility, but require a high degree of expertise to use effectively.

A select query example

Let’s quickly run through a second select query, answering the question: Which countries are in the top 5% in terms of area?

1. Double-click Create Query In Design View.
2. Select the Countries table, click Add, and then click Close.
3. Click Name in the list of fields, Ctrl-click Area, and drag the two fields to the grid.
4. In the Area column, choose Descending from the Sort drop-down box.
5. In the Top Values box on the toolbar, type 5% (or select it from the drop-down list).
6. Click Run to execute the query.

Calculated fields

How about the question: Which five countries have the lowest population density? We don’t have a Population Density field in our table, so how can we calculate it?

We do it by creating a new field which becomes part of our results (note, though, that the field does not become part of the existing table structure). Here’s how:

1. Double-click Create Query In Design View.
2. Select the Countries table, click Add, and then click Close.
3. Drag the Name and Population fields onto the grid.
4. In the top row of the empty third column on the grid, type:

PopDensity: population/area

and press Enter. We’re creating a new column (PopDensity) whose values will be calculated by dividing each country’s population by its area. By the way, if you can’t see the entire contents of this column, drag the right-hand edge of the column header to the right to resize it.

5. In the Top Values box on the toolbar, type 5.
6. Click the Totals button (it has the Sigma sign on it). A new Totals row appears in the grid.
7. In the PopDensity column choose Ascending from the Sort box.
8. Click Run to execute the query.

The results show the names, population and population density for the world’s least populated countries.

This Select query in Access finds the five most sparsely populated countries in the world using a calculated field, PopDen

An action query

Let’s finish up by converting that last query into an action query. This query doesn’t merely provide us with the results; it saves them in a new table which includes a PopDensity field in its structure. The original table remains intact and unchanged; it’s the new results table which has the extended structure.

1. If you have the query results still showing, click the View button at the left end of the toolbar to view the query in design mode once more. If you have already closed the query, recreate it.
2. Click the down arrow on the Query Type button on the toolbar and select Make-Table Query.
3. Name the query Sparsely Populated, leave the Current Database option selected, and click OK.
4. Run the query and, when prompted, say Yes to create the new table.
5. Close the query, click Tables in the Objects panel, and you’ll see a new table called Sparsely Populated. Double-click it to see the query results in the new table. You’ll notice the new table contains five records (the most sparsely populated five countries) and three fields – Name, Population and PopDensity.