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.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState