Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.You may find that the database included in LibreOffice / OpenOffice delights you as much as it has me. This page tries to help you use it.
Remember that Libre Office, including its Base, is free. Don't let that fool you, though. Big organizations, governmental and civilian, have adopted it as their standard office suite... and saving million$, but still Getting The Job Done. And making things easy for users on different platforms... Linux, Mac, Windows all spoken here!
There's more about Base 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 fully explained, and there are more tips, at my Power Browsing page.)
Page contents © TK Boyd, Sheepdog Software, 3/06-10/20.
Overhauled 25 Oct 20 to be right for LibreOffice Base using embedded Firebird. (Do switch to Firebird, when and as you can!) Not every graphic was updated.
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. Leave your earlier work alone... make a COPY of the .odb file, create a new folder for this exercise called "fdb1query1", and paste the .odb file into the new folder.
Rename the file FDB004-ForQuery1.odb
If the table doesn't already have some records, 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.
Open the copy you've made of the database of names, addresses and phone numbers.
In addition to records it already has, you may need to add some more now. We need at least one each with "AZ", "CA", and "WY" in for "StateOrProvince".
We also want a few records with "1" in the PhonePriority field, and at least one each with 2, 4 and 6 there.
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 Base 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.
Click Next.
In step 4, assign JUST the following alias:
LastName: Last name
This step is of no great importance in this instance, but it will give you nicer column headings in the results of running the query. Previously, I suggested also changing the others, to "PhoneNumber: "Phone number" and "Name", but just now (26 Oct 20) that caused a weird error. ("xx must be visible..."), and doing just "LastName" will illustrate aliases and satisfy honor. (You may be able to edit the query, once it has been saved once.) (I was on a Win 10 machine, 64 bit, using "Version: 6.3.6.2 (x64) Build ID: 2196df99b074d8a661f4036fca8fa0cbfa33a497 CPU threads: 4; OS: Windows 10.0; UI render: default; VCL: win; Locale: en-US (en_US); UI-Language: en-GB Calc: threaded", if you care!)
Whew! Is NOTHING simple?? Click "Next", to go on to step 5.
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. For now, I'll show you the "bodge" answer to that want. "Reports" would be a better answer, and you will learn how to do them, if you read some more of my tutorials. But they are a story for another time. And the following has it's place, anyway.
Open an ordinary Libre Office text document, i.e. fire up the Libre Office word processor, Write. From Base's project manager window, drag the query name to a blank spot on the text document. Answer some of the questions in the dialog box.
On your first visit, don't try to be clever. Don't do much. Just, say, use the ">" between the "Database" and "Table" boxes to move, say, the filed names "Phone number" and "LastName" to the "Table" box, and you will obtain a text document with the results of the query on it in a table. (Don't be alarmed when, the next time you invoke the dialog box, it comes up as you left it this time. You can move field names back to where they were before.
When you've made a simple table appear on the Writer document, play with the process a bit. (If you want the columns' names to appear as column headings, just tick the "Insert table heading" option. There's no need to mess with e properties there.)
A detail on "answer the questions...": You may find yourself moving all of the fields from the "Table columns" box to the "Database columns" box, and then moving some back again, one field at a time, until 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.))
By the way... there's nothing to stop you from creating several tables on a single "sheet" of Writer "paper". You can drag several queries (or even one query several times!) to the same Writer 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 Writer documents.
---
What we've just done illustrated something special to Libre Office and 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. (This remains true when you graduate to using "Reports".) When we worked with forms, earlier, did you notice that a Base form is actually "just" a Writer document? A fancy one, with clever, active, elements, admittedly... but still: A Writer document.
One great benefit of the tight integration is that whenever you learn something to solve whatever is challenging you today, 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 Writer 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 in Writer documents 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 Writer document again if I need an updated phone list. (Again, when you use Reports, this want is assuaged.)
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, START by changing the "Table" selected by the drop-down at the top of the dialog. Make it "Table:NameAddrPhone"... otherwise you won't have all of the table's fields available!
Otherwise, do what you did before, but include the PhonePriority field for the query, too.
In step 3 set a search condition which says "PhonePriority is equal to" 1.
Call this list "Phone list-priority 1 numbers".
When you drag the query to a Writer page in order to create a printable version of the list, there's no reason to include the PhonePriority field.
Success! Moving on.....
Open the database, if it isn't open already.
Click on "Use wizard to create query" again.
In step 1, field selection, remember you may need to start by changing the "Table" selected by the drop-down at the top of the dialog. Make it "Table:NameAddrPhone".
Still in Step 1, double click on the LastName, RestOfName and StateOrProvince fields. Click Next.
In step 2, set the sorting order as...
StateOrProvince, then LastName, then RestOfName
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, I once again had trouble if I tried to use aliases. They "should" work, and are "nice"... but they aren't worth fighting with now. When the rest has worked for you, see how far you can get! 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 "Copy".
Right click elsewhere in the "Queries" panel. Click "Paste". change the name to "Numbers in CA", press enter.
Right click on your "Numbers in CA" 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, just add CA (No quote marks or anything else around that are needed... but the next time you open the designer, you'll find apostrophes around the CA.) in the "criterion" cell of the "StateOrProvince" column. Once you've done that, save the query, close the query "designer" (i.e. editor), run the query.
... OR you can just run the query directly from within the editor. There's an icon for that on the toolbar at the top of the window. It's tooltip hint is "Run query". Look for "Run query" in the Edit sub-menu, and you can see the icon and the shortcut key.) (Note also, if you go the "Run in editor" route, the result of the query appears in a new pane at the top of the Query designer.)
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", (it's in the "View" sub-menu") you'll see the SQL for the query you have specified so far.
I should mention here that this guide is now intended for Libre Office Base running the embedded Firebird engine. When you start delving into the underlying SQL, the HSQL engine may well want something slightly different, and "slight" differences can matter".
The SQL for our "Numbers in CA" query is:
SELECT "LastName" "LastName", "RestOfName" "RestOfName", "StateOrProvince" "StateOrProvince" FROM "NameAddrPhone" WHERE "StateOrProvince" = 'CA' ORDER BY "StateOrProvince" ASC, "LastName" ASC, "RestOfName" ASC
I suspect we are seeing each field name twice because the second is where the alias would go... if we "crack" using aliases! (A more explicit SQL version would be "SELECT "LastName" AS "LastName", "RestOfName" AS "RestOfName",..." but it may be acceptable to leave the "AS" implied. And if we were USING aliases which were different from the underlying field name the SQL would look something like... "SELECT "LastName" AS "Last name", "RestOfName" AS "Rest of name",..."
---
(The next paragraph is DATED! I would, 10/2020, suggest that you use Libre Office and the Firebird embedded engine. While its SQL is similar to the HSQL, it won't be the same, and you should go to Firebird documentation.)
The HSQLDB.ORG reference manual is a wonderful resource for those who like fundamental definitions of how software works. Have a look, for instance at the intro to data types. . (Once you master how the information is presented. Yes, it really is worth mastering that syntax.)
If you start using some of the more esoteric material from the HSQL manual, you may need to learn about turning off Base's SQL syntax checking... but don't let that worry you over-much if you are just getting started.
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.
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.
PLEASE >>> Click here to visit editor's Sheepdog Software freeware, shareware pages <<< PLEASE
If you liked this Libre Office Base 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.
Page has been tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes.
AND passes...
. . . . . P a g e . . . E n d s . . . . .