Creating Queries in OpenOffice.org Base

Queries are the database equivalent of filters in a spreadsheet. Just as a filter can limit and reorganize the information displayed in a spreadsheet, so a query limits and reorganizes the information in a database. Either can be an efficient way of finding the information you want, especially when you're dealing with thousands of records. Of course, in the hands of an expert, queries can be far more precise — and complicated — than a filter, but, if you are using OpenOffice.org Base, then the analogy generally holds true, although queries are slightly more complicated than filters to set up. When saved in Base for long term use, a query becomes a view.

You can create a query in any database set up in Base. In fact, Base offers you three different ways to write a query: in a wizard, which guides you through the process; in Design View, which provides a minimal amount of guidance, and in SQL View, which provides no guidance at all. If you are a database expert, you might prefer Design or SQL View. However, like the Function Wizard in Calc or the introductory wizard in Impress, Base's Query Wizard has the advantage of helping you organize your thoughts while requiring no previous knowledge. For most people or purposes, there is no reason not to use the wizard.

Creating a Query

To create a query, open a database and click Queries in the left hand pane. The query page is organized like the other pages in Base, with a list of available tasks in the top middle, and a list of existing items in the bottom middle.

Note that, for some reason, Base allows only one query to be listed at a time. If one already exists, it is highlighted in the Queries pane, and any new queries will be sub-queries of the existing one. If that is not what you want, right-click the existing query and choose Create as View from the Context menu to rename the existing query and have it moved to the Tables page. Only when the Queries pane is blank should you click Use Wizard to Create Query in the Tasks pane, and open the Query Wizard.

The first step in creating the query is to select the fields that you want to display in the Query. Note that these are not only the fields that you will use for filtering information, but all the fields you see when using the query. After all, if you are searching for all the addresses in Canada in a list of addresses, you don't want to just display the Country or Region in the Query, because that would just give you a list of records that say "Canada" — most likely, you want at least the names from the address, and probably the street, city, etc. as well. As in other parts of Base, you use the left and right arrow buttons to move the available fields to the fields you are going to use, and the up and down buttons to arrange them in order.

The next step is to sort the order in which responses to your query will display. This page of the wizard, you might notice, is almost identical to the sorting dialogs found in Calc and other parts of OpenOffice.org, allowing you to select up to four fields for sorting. For example, if you were searching for Canadian results in an address database, you might want to display query results sorted by last name, and then by first name.

In the third page of the wizard, you actually set up the query. Again, the dialog should be familiar to you — if not from OpenOffice.org, then in email searches in programs like Evolution or KMail. You can define up to three criteria, setting the field, the condition, and the value. In the case of the Canadian address query, the criteria would be simple: You would select The CountryorRegion field, and specify that it should be equal to Canada. Since you have only one condition, you wouldn't even need to remember to set the conditions to Match any of the following, the way you might in a more complicated query.

On the Detail or Summary page, you have the option of choosing a Detailed Query, in which all records that match the query are displayed, or a Summary Query, in which only the sum, average, minimum or maximum value is shown. For most purposes, you probably want a Detailed Query, especially if the information in the database is non-numerical, as it is with an address book search.

At this point, Base skips two steps in its standard wizard because they are not relevant to queries, and jumps to Aliases. The purpose of this page is to have the fields display in human-readable form, adding small touches such as spaces between words and other grammatical niceties. It is similar to a page in the Report Wizard called Labelling fields.

Finally, you are given an overview of your choices. Take a moment to look over what you have done, using the Back button to make any changes. Once the query is created, the only way to make changes is through the Design View, which can teach you how to use the view, but is still nowhere near as user-friendly as the wizard. You also have the option here to view the new query immediately, or else open it in Design View to add more advanced touches that are beyond the capacity of the wizard.

Using Queries

In order to create additional queries, you should either change any query to a view or delete it when you are finished with it. In either form, you can view it on the far right of the window by selecting Document. Or, if you prefer, you can double-click it to see the Query in a Table Data view in a separate window. To print the query as a report, right-click and select Report Wizard from the context menu.

Queries can become far more complex in other databases, especially when you use functions. In Base, though, they are simple enough that anybody can use them Should you go on to use another database, like PostgrSQL or MySQL, after using queries in Base, you should have a better understanding of what to expect. Meanwhile if you choose to go no further, you have a useful tool that you can quickly customize.