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.

Field selection

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.

Sorting order

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.

Search conditions

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.

Detail summary

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.

Queries aliases

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.

Overview

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.

AttachmentSize
detail-summary.png36.34 KB
field-selection.png42.97 KB
overview.png55.23 KB
queries-aliases.png45.07 KB
search-conditions.png38.73 KB
sorting-order.png44.41 KB
______________________

-- Bruce Byfield (nanday)

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

You should explain the

Anonymous's picture

You should explain the conditions. Base certainly doesn't help the noobies with this.

User-friendly database

Anonymous's picture

Hi

I understand how you can create a query in OpenOffice Base. But, is it possible to make the query as user-friendly for a friend that doesn't know much about computers. Changing the query through a list box etc.

If i am setting up a database

Anonymous's picture

If i am setting up a database for contacts, and i want to make a query condition to be the first name, do i have to type in all the names?

What about filters

OldSQL_hacker's picture

I understand queries to a database, been doing it for 20+ years. I'm relatively new to spreadsheets. How about an article on filters for a spreadsheet.

Webinar
One Click, Universal Protection: Implementing Centralized Security Policies on Linux Systems

As Linux continues to play an ever increasing role in corporate data centers and institutions, ensuring the integrity and protection of these systems must be a priority. With 60% of the world's websites and an increasing share of organization's mission-critical workloads running on Linux, failing to stop malware and other advanced threats on Linux can increasingly impact an organization's reputation and bottom line.

Learn More

Sponsored by Bit9

Webinar
Linux Backup and Recovery Webinar

Most companies incorporate backup procedures for critical data, which can be restored quickly if a loss occurs. However, fewer companies are prepared for catastrophic system failures, in which they lose all data, the entire operating system, applications, settings, patches and more, reducing their system(s) to “bare metal.” After all, before data can be restored to a system, there must be a system to restore it to.

In this one hour webinar, learn how to enhance your existing backup strategies for better disaster recovery preparedness using Storix System Backup Administrator (SBAdmin), a highly flexible bare-metal recovery solution for UNIX and Linux systems.

Learn More

Sponsored by Storix