AUTHOR'S MAIN SITE   > > > > >   TABLE OF CONTENTS for version 2 Open Office database tutorials.

Queries- a first look
Open Office 2 Base (database) Tutorials

You may find that the database being shipped with OpenOffice (ver.2 and higher) delights you as much as it has me. This page tries to help you use it.

Forget anything you may have heard about Adabas, which came with Star Office, the commercial version of Open Office 1. The current Open Office's database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including ooBase, is free! But don't let that fool you. And it's not new. Big organizations, government and civilian, are adopting it as their standard office suite... and saving million$, but still Getting The Job Done.

There's more about ooBase in the main index to this material.

This page is "browser friendly". Make your browser window as wide as you want it. The text will flow nicely for you. It is easier to read in a narrow window. With most browsers, pressing plus, minus or zero while the control key (ctrl) is held down will change the texts size. (Enlarge, reduce, restore to default, respectively.) (This is more fully explained, and there's another tip, at my Power Browsing page.)

Page contents © TK Boyd, Sheepdog Software ®, 2/06-7/09.




The Plan_______________

In this tutorial, we're going to see queries in action. At the level of this tutorial, you may be left wondering why you would want to bother with a query. The things we are going to do are very simple, and can be accomplished other ways. However, queries are very powerful, and once you can "walk", there are things you can do "at a run" that are very useful. (And hard to do other ways.)

The tutorial will use the table with name, address and phone number fields prepared during the introductory tutorial on table creation. (I suggested that you save that database as FDB004). You will need to fill some imaginary (or real!) data into the table. This can be done directly, or you can use one of the forms developed in the "Simple Form" tutorial.

Queries process the data in tables. Every database will have at least one table, some with have many. Tables are the essential foundation of any database. As your skills improve, you will probably use the tables directly less and less... but that doesn't change their fundamental importance.

A query is a set of rules for fetching information from a table, or from several tables at once. The result of a query is itself a tables. It consists of a set of records, organized in rows (one per record) of columns (one per field). Usually, the query result table is unlike the database's other tables in that you don't keep it for very long. If you use your operating system's copy/ paste facility, you might think of the tables arising from queries as being the same sort of stuff as things that reside briefly on your system clipboard from time to time.

In this tutorial, we are going to develop two queries. The first will be useful for creating phone number lists from the database. The second will list people according to what state they live in. (The later might be useful, if, say, you lived in New York, but were planning a trip to California and didn't want to overlook any friends living there.

Reports are another way to create such lists, but they come with extra constraints to match their extra powers. Queries are perhaps more generally useful, and you should master them in any case. You should master reports, too, eventually, but I think queries should come first. For one thing, a report can be based on a query... if you know how to make the query for the report!

Just before we start, let me emphasize something that is hinted at in the above: In database work, you need to master tables, forms, queries and reports. They are the four "cornerstones" of database literacy.


Executing the Plan- Phone numbers list____________

Open the database of names, addresses and phone numbers (FDB004) prepared during the introductory tutorial on table creation.

Get yourself to the project manager window. That's the one with three main panes: Database, Tasks, and a third, probably labeled "Forms" at the moment. If the third pane does not say "Forms" at the moment, then in the pane labeled "Database" (left column) click on "Queries", and the "Queries" pane should appear, although it will be empty at the moment. (If you want to, visit my introduction to the ooBase main project manager window.)

Click on the "Use wizard to create query" task.

At the moment, your database only has one table, so the table selection is moot.

In step 1, in the Available Fields column, double click on the following, in this order...

PhoneNumber
RestOfName
LastName

Click Next.

In step 2, set the query to sort first by LastName, and then by RestOfName. Click Next.

In step 3, for now, do nothing. Just click Next.

In step 4, assign aliases as follows:

PhoneNumber: Phone number
RestOfName: Name
LastName: Last name

This step is of no great importance in this instance, but it will give you nicer column headings in the result table. Click Next.

In step 8, make the query's name "Phone list- all numbers". You don't need to do anything else except click Finish. (If you want to modify the query, you will still be able to do that later.)

A data sheet should come up... records in a grid, one row per record, one field per column. Note that the records are sorted.

So. We can get the list of phone numbers onto the screen. What about printing it out. Shock! Dismay! There's no "Print" option in the menus!!

Fear not.

Open an ordinary Open Office text document, i.e. fire up the Open Office word processor, ooWrite. From ooBase's project manager window, drag the query name to a blank spot on the text document. Answer the questions in the dialog box, and you will obtain a text document with the results of the query on it in a table. (A detail on "answer the questions...": You will possibly have to move all of the fields from the "Table columns" box to the "Database columns" box, and then move them back again, one field at a time, so that you get them in the order you want. (It is also possible to re-sequence the columns in the table after it has been generated.))

This is an instance of something quite special to Open Office: The database management software is very tightly integrated with the other parts of the office suite. Instead of having a separate editor to manage output from the database, the output is done via a wordprocessor document. When we worked with forms, earlier, did you notice that an ooBase form is actually "just" a ooWriter document? A fancy one, with clever, active, elements, admittedly... but still: An ooWriter document.

One great benefit of the tight integration is that whenever you learn something to solve "problem A", you are learning things which will be useful in other circumstances, too.

The way things are laid out after you drop a query onto a blank ooWriter page may not entirely suit you. But whatever it takes to get the table as you want it for printing will be the same as what it takes to manipulate other tables from other sources at any future date.

Note: Once the data has been "harvested" with a query, it doesn't maintain a link with the database. I'll bet there's a way to make the list of phone numbers stay in sync with subsequent edits to the database, but I like the simple life, and will regenerate the ooWriter document again if I need an updated phone list.

By the way... there's nothing to stop you from creating several tables on a single "sheet" of ooWriter "paper". You can drag several queries (or even one query several times!) to the same ooWriter page. You might not do that often in routine use of the product, but while you are learning, you don't need to keep starting new ooWriter documents.




We're now going to do something not explicitly mentioned in the plan at the start of this tutorial.

If you cast your mind back to the initial planning of the database, you will remember why we created the "PhonePriority" field. The idea was that we might want to create a list of just the numbers we phone often. By putting "1" in the "PhonePriority" field of those numbers, and something else in that field for other records, we can accomplish the selective list.

Make a new query, Do exactly what you did before, but....

In step 1, include the PhonePriority field, too.

In step 3 set a search condition which says PhonePriority must equal 1

When you drag the query to an ooWriter page in order to create a printable version of the list, there's no reason to include the PhonePriority field.

Success! Moving on.....



Executing the Plan, part two- Names- by- state list_________

Open the database, if it isn't open already.

Click on "Use wizard to create query" again.

In step 1, field selection, double click on the LastName, RestOfName and StateOrProvince fields. Click Next.

In step 2, set the sorting order as...

StateOrProvince, then
LastName, then
RestOfName and 

Click Next.

Change nothing in step 3. Click Next.

You will go straight to step 7. This isn't what I wanted or expected, to be honest. It would be nice if we could use the "Grouping" feature.... but we don't need it, and I couldn't get it to become available. Sigh. (It is a story for another time, but if, after you finish this query, you use the report creating wizard, ask it to create a report based on this query, and select "Group by StateOrProvince" at the appropriate moment, then you'll get a grouped report quite easily.)

In step 7, change the aliases again, to more human-friendly headings for the results table columns. Click Next.

In step 8, name the query "Names by state."

Voila! Done! You'll get a list of everyone's phone numbers, but with all the people from Arizona in one section, the people from Arkansas in another, etc.




A little bonus for all of you good people who are still reading....

Suppose you have many, many names in your database, and you want just the names of people from California?

Close anything which may be open, apart from the project manager.

Right click on your "Names by state" query. Click on "Edit".

The fancy control panel which comes up gives you lots of opportunities. To make the query list just people from California, then, assuming that in all the records, you've entered the state data as "CA" (without the quotes), just add CA in the "criterion" cell of the "StateOrProvince" column. Once you've done that, you can save the query, close the query editor, run the query.... OR you can just run the query directly from within the editor. There's an icon which looks like two sheets with writing under a green tick mark on the toolbar at the top of the window. It's tooltip hint is "Run query".

More "unnecessary" stuff, but cool: Real men don't mess about with GUI design tools, they chew bullets and write raw code. For this database, that means writing SQL code. Joking apart: Sometimes raw code is the fastest, clearest, most direct route to where you want to be. If, in the query editor, you "Switch design view off", you'll see the SQL for the query you have specified so far. (Our example's SQL is:

SELECT "LastName", "StateOrProvince", "RestOfName"
FROM "NameAddrPhone" "NameAddrPhone"
WHERE ( ( "StateOrProvince" = 'CA' ) )
ORDER BY "StateOrProvince" ASC, "RestOfName" ASC

The HSQLDB.ORG reference manual is a wonderful resource for those who like fundamental definitions of how software works. Have a look at the declaration of what you can do with SELECT.... it is awesome. (Once you master how the information is presented. Yes, it really is worth mastering that syntax.) In the terms of that definition....

"LastName", "StateOrProvince", "RestOfName"...
    - - expression, consisting of columnAliases

"NameAddrPhone"...
    - - tableList

"StateOrProvince" = 'CA'...
    - - selectStatement giving one value = value

"StateOrProvince"...
    - - columnAlias

("ASC" for "ascending")

If you start using some of the more esoteric material from the HSQL manual, you may need to learn about turning off ooBase's SQL syntax checking... but don't let that worry you over-much if you are just getting started.

Concluding remarks_________

Do remember I said at the beginning that these queries are not the fanciest queries you can make. But I hope they have started you along the path of understanding queries, and their use. Queries allow you to extract the records you want from the underlying tables which are the foundation of your database. They allow you to extract just the fields you need, and they let you organize the data to suit your needs.





Editorial Philosophy

I dislike 'fancy' websites with more concern for a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Of course, an attractive site WITH content deserves praise... as long as that pretty face doesn't cost download time. In any case....

I am trying to present this material in a format which makes it easy for you to USE it. There are two aspects to that: The way it is split up, and the way it is posted. See the main index to this material for more information about the way it is split up, and the way it is posted.


Ad from page's editor: Yes.. I do enjoy compiling these things for you... I hope they are helpful. However.. this doesn't pay my bills!!! If you find this stuff useful, (and you run an MS-DOS or Windows PC) please visit my freeware and shareware page, download something, and circulate it for me? Links on your page to this page would also be appreciated!

PLEASE >>> Click here to visit editor's Sheepdog Software (tm) freeware, shareware pages <<< PLEASE


If you liked this ooBase tutorial, see the main index for information other help from the same author.

Editor's email address. Suggestions welcomed!     - - -    Want a site hosted, or email? I like 1&1's services.




Valid HTML 4.01 Transitional Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org


One last bit of advice: Be sure you know all you need to about spyware.

. . . . . P a g e . . . E n d s . . . . .