Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes. There were two "unknown attributes" in the Google+ button code. Sigh.
Bookmark this on Delicious
Recommend toStumbleUpon
You may find that the database included in OpenOffice delights you as much as it has me. This page tries to help you use it.
Remember that Open Office, including ooBase, 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 ooBase in the main index to this material. Adabas? Star Office? Ancient history.
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, 2/06- 12/12.
I must warn you that I should have built up to this exercise in fewer, smaller steps! Only as I got into it did I discover just how much is involved in the "simple" task I proposed accomplishing!! None-the-less, what I describe DOES work, and if you struggle though this to the end, you will have covered a lot of ground!!!
I will also add that I have taken even more care than usual with this rather large tutorial. It will repay whatever work you put into it, but don't imagine that you'll be able to skim through it in ten minutes. It does cover, more or less from scratch....
I accidentally wrote a second page addressing most of the things this page addresses. You might want to start with that one. It doesn't "hold your hand" as much as this one does, and it doesn't cover everything that is here... but it is more focused. In particular, that tutorial uses a form for the final data presentation, vs the report used in this tutorial. On the other hand, it doesn't cover as much ground as this one does. You choose! Or do them both; one will help you with the other.
In fact, I've written several tutorials with "relationships" (as in "relational database") at their core, and so have produced a sub-menu page for these related essays.
For the sake of this tutorial, we are going to assume you are interested in classical music. We're going to build a database of information about an number of famous pieces. For each, we're going to have....
Piece's name Composer's name Composer's date of birth
The job could be done with a single table. Doing it as we will is slight overkill... but it will illustrate the techniques you would use in a situation where the they were essential.
The "trick" is to use multiple tables.
We will have one table with information about composers: their name and year of birth. There will also be an "ID" field which will hold a code for the composer, and no two composers will have the same code. While I can think of no two significant composers with the same name, in many situations, there will be duplications in things like name. An ID field is often necessary or useful, even when it is merely filled with an arbitrary unique number, as we did for the ID field in the introductory tutorial in which we set up our first table.
We will have a second table with the names of pieces of music. In this table, we will indicate the composer with the composer code defined in the first table.
You may have already spotted one advantage of this approach: Most composers are remembered for more than one piece. Instead of typing out their names repeatedly, you only need to enter the (shorter) code for the composer in the record for the piece. This also helps ensure that you don't introduce variations on the names, e.g. enter....
You and I would realize those are both by the same composer, but the computer would "see" them as by different composers.
With these two simple tables, we will be able to print out reports like....
Moonlight Sonata, Beethoven, born 1770
Messiah, Handel, born 1685
Zadok the Priest, Handel, born 1685
Meistersinger, Wagner, born 1813
"So what?" you may ask... but look closely. The table starts with a list of the pieces from the second table. Simple enough... but: against each is listed information drawn from the second table. "Handel, born 1685" appears on two lines in the output we want, even though it is in the computer just once.
Even the little example above is enough to illustrate one of the great reasons for using multiple tables. Although "Handel, born 1685" appears twice, it would have been entered into the database only once. In the simpler answer collecting the data, the one with just a single table, the "Handel, born 1685" would have to be entered twice... and the chance of typos arises. Of course, typos can arise in the two table system, too, but at least if "Handel" is misspelt anywhere, it will be misspelt everywhere, easier to notice, and, once noticed, more easily fixed.
So! Let's build the tables and the report to display what's in them.
Start Base, electing to create a new database.
Elect to have the database registered, to open the database, but not to create tables.
Save the database as FDB005 in a folder called FDB005, in a folder called FDB, in your "My Documents". ("FDB" for "Free Data Base")
When the database has been created, you should see the project manager window. (This is the one with three main panes: Database, Tasks, and a third, possibly labeled "Forms" at the moment.) In the pane labeled "Database" (left column) click on "Tables", and the "Forms" pane should be re-labeled "Tables".
From "Tasks" click on "Create table in design view..."
A string grid should come up. Fill it in as follows....
CompID / Text[VARCHAR] YOB / Integer[INTEGER] CompName / Text[VARCHAR]
Now go back to the first line (CompID) by clicking on it. In the properties boxes at the bottom of the screen, change the length of CompID to 7. Right click on the gray rectangle just to the left of the "CompID", and click on the "Primary Key" choice. You'll know you've succeeded if a little golden key appears in the gray box.
Leave the second field alone.
Change the third field's length to 25. (Here and elsewhere, leave other things as they are.)
Click File | Save (in the table design window's menu), and name the table CompNameYOB. Close the table design window.
Next we're going to do something similar to create a table to hold the names of the pieces of music, with their composers. Re-click the same "Create table in design view..." as you used a moment ago.
In this table we need:
OpusID- Integer[INTEGER] OpCom- Text[VARCHAR] OpName- Text[VARCHAR]
Adjust the properties of the first field, making AutoValue "Yes". Also set that field as the primary key.
Set the lengths of the OpCom and OpName fields to 7 and 25 respectively. Save table as OpusInfo. Close the table design window.
Next, we're going to define a relationship between the tables. It can be done later, after some data has been entered, but if it is, the data in the tables must be okay by the defined relationship, and if you have any typos in the data, you can't define the relationship until you find and fix the typos. (Take my word for it... define relationships first!) Once the relationship is defined, you cannot enter invalid data, which is one of the reasons for defining the relationship. (You can still make mistakes, e.g. saying that Wagner wrote Messiah, but some mistakes become impossible.) (When you have set up some relationships, you may find data entry a pain, if you like to, say, do the composer ID for several records and then go back and fill in other fields for them. If while entering data, you cannot move out of a record until every field that is part of some relationship has something acceptable in it, you can "cheat", by first giving yourself a "simple false entry", something like "x", to temporarily satisfy the fields you want to fill in later.)
Close everything except the project manager window. With the menu, open Tools | Relationships. Add both of your tables to the Relation Design window with the Add Tables dialog, which should have popped up with the Relation Design window. You can now close the "Add Tables" dialog.
Put your mouse pointer on the CompID field in the CompNameYOB table. Press down, and hold down until further notice, the left mouse button. Move the pointer across to the OpCom field of the OpusInfo table. Release the mouse button. You should have done what would normally be described as trying to drag CompID onto OpCom. Instead of CompID moving, though, you should now see a line between the edge of the CompNameYOB box (at the level of CompID) and the OpusInfo box (at the level of OpCom). You can drag the boxes around if the line is "squashed". At the CompID end of the line, you should see a "1", and at the OpCom end of the line, you should see an "n". Don't close the Relation Design window just yet. I'll tell you when it's time.
The line says that there is now a relationship defined between the tables. It is a "1 to many" relationship. That means that a given value only appears once in CompNameYOB, but it can appear many times in OpusInfo. In other words, there can be more than one record in OpusInfo with the same data in their OpCom fields.
An aside: We can say "the CompID field in the CompNameYOB table" if we wish. We can, more concisely, say "CompNameYOB.CompID" when we know that method of specifying fields.
What's the relationship good for? We'll see in detail later in this tutorial. We'll see that it prevents you from making an entry in OpusInfo.OpCom, e.g. "gf1865a", unless that composer code has been created in CompNameDOB.CompID. The relationship is also critical to our overall goal of listing data from two tables in a single report.
By the way: If you don't like defining the relationships by dragging, you can, from the Relation Design window menu bar, use Insert | Relation. Whichever method you use, be sure that you set the relationship up the right "way around". The "1" must be on the CompNameYOB end of the line signifying the relationship. You can define it the other way around, but you wouldn't then be able to enter sensible data.
Right click on the line between the boxes signifying the tables. Click on Edit.
Now you can see some properties of the relationship which weren't evident from the relationship diagram. You don't need to change any of them just now, but keep them in mind as things you ought to learn about eventually. They are tools which help keep your data valid. At the moment, if you have, say, Wagner in the CompNameYOB table, and a piece by him in the OpusInfo table, then you can't change or delete CompNameYOB.CompID. This is a good thing. It ensures that every OpusInfo.OpCom entry has a corresponding CompNameYOB.CompID entry. If you change the relationship's "Update options" property to Update Cascade, then you can change a CompNameYOB.CompID entry... and OpusInfo.OpCom entries with the same datum will be changed as well... but you won't see that they've changed if you happen to have the OpusInfo table open... unless you master further tricks. So for now, leave the options set to "no action", which prevents bad data getting into your tables.
Okay! Now you should close the Relation Design window.
(There's more on relationships and joins in a short(er) page I've done talking about these important concepts in general terms. They are not the same thing, but the differences constantly blur in my mind, and I hope to help you fight that problem.
In the "Tables" pane of the project manager, double click on "CompNameYOB" to open the table for data entry, and enter.....
lb1770a / 1770 / Beethoven gh1685a / 1685 / Handel rw1813a / 1813 / Wagner
(You may know that editing data directly in tables is a Bad Idea... it is better to use a form... but for little exercises like this, there's no harm in taking the shortcut.)
Let's look at what we've entered:
The CompID is made up as follows....
What is the "a" for? If there were two "gf"'s for 1685, then one would be gf1685a, the other gf1685b. The CompID must be different for every composer. You could simply use arbitrary numbers, e.g. "001" for JS Bach, "002" for Stravinsky, "003" for Paul McCartney... but it will be easier to enter the composer ID codes in the other table if they are at least somewhat meaningful.
Now put the following into the OpusInfo table. Note that the first field, OpusID will be just an arbitrary number, and that the computer will "type" it for you. (If it doesn't, you didn't set the table up correctly. Close the table. Re-open the table's definition, and change the properties for the OpusID field. Re-open the table for data entry.)
0 / lb1770a / Moonlight Sonata 1 / gh1685a / Messiah 2 / gh1685a / Zadok the Priest 3 / rw1813a / Meistersinger
For the above, you probably just copied from here. When you are entering data "for real", there is nothing to stop you from having both tables open on the screen, so that you can refer to the CompNameYOB table while entering data in the OpusInfo table. (Unless you have a very good memory, you will need to check the OpCom codes as you go along... unless you develop this database further. It is possible to make a data entry form for OpusInfo which puts a listbox in the CompID field. When you go to enter a CompID, you just click on the box and a list of the possible codes appears, drawn (by ooBase) from the CompNameYOB table.)
Try entering a composer code that doesn't exist, or merely mis-typing one that does exist. Because of the relationship you defined, you can't enter the data. Want to add a piece by Aaron Copeland? You can, but you have to put him in the CompNameYOB table first.
A little re-cap: We now have two tables with some data about music and composers. Next, we want to create a report which draws information from both tables.
Before we try to create the report, we are going to have to create a query. You will be given the details on a moment. Queries are one of the four cornerstones of database work. In a nutshell, a query creates a new table which draws its data from one or more existing tables. I've also done a quick tutorial introducing queries, which you may want to visit now if the whole idea of queries is new to you... but I will give "keystroke- by- keystroke" instructions here.
A table resulting from a query is often a transient entity, essential to the internal workings of a job, but not "seen" on the surface of things.
The query we are going to make doesn't do justice to the general theory and practice of queries... but it will accomplish what we need!
Get yourself to the project manager window, the one with three main panes. In the left hand pane (labeled "Database") click on "Queries", and the third pane should be re-labeled "Queries".
From "Tasks" click on "Use wizard to create..."
If the CompNameYOB table isn't the one showing in the "Tables" field on the window, change what's there to CompNameYOB.
From the CompNameYOB table select the YOB and CompName fields. I.e. get those fields into the "Fields in Query" list, the right hand pane of the window.
Notice that which step you are on is shown in the window's left hand pane.
Still in Step 1 of the wizard, change the table to OpusInfo. Click on the OpName field, click on the arrow which moves it to the "Fields in Query" list. Now click the "Next" button to move on to Step 2.
You don't need to set a sorting order, so just click "Next" again to move to step 3. (Actually, I was wrong when I said "You don't need..." This is the place to do it if you want the records your final report to appear in some particular order... but for now, specify no sorting. When everything else is working, you are allowed to go back, revise the query specification.)
You don't need to set search conditions, so click "Next" again to move to step 4.
You want a "Detailed query", not a "Summary query". Click Next again, and you will be "fast forwarded to step 7, aliases.
You won't need to fiddle with the aliases, so click Next again.
In step 8, you assign a name to the query. Use "CombineTables", leave "Display Query" selected, click "Finish". (At last!) You should see something quite like the result we are trying to achieve.... But!...
We're getting some weird duplication. We see every composer listed against every piece of music!
Close the window with the result table in it.
Go to the project manager window which is probably still on your screen, and probably showing an entry for the CombineTables query. You may have to re-select the Queries category in the Tasks pane.
Right click on the entry for "Combine Tables", and select Edit from the choices.
That should open a window titled "CombineTables". In the top part of it, you should see the two tables, with their fields. Across the bottom of the window is a string grid, which we will not be doing anything with. (Whew... there's all sorts of stuff down there!)
Put the mouse pointer on top of CompID in the CompNameYOB table. Press down and hold the left mouse button. Move the mouse pointer until it is over the OpCom field in the OpusInfo table. Release the mouse. You should see a line between the table's boxes. That line describes a join between the tables, for the query. It is quite like the relationship that we worked with earlier, but not exactly the same thing. I may be misusing the term, but I think that such a join (which term is used in it's precise sense here) is also sometimes called a link. I'm not going to go into the theory and practice of raw SQL code here, but if you want to click View (in the window's menu bar), and click on Switch Design View..., you will be taken over to the SQL for the query we've created.....
SELECT "CompNameYOB"."YOB", "CompNameYOB"."CompName", "OpusInfo"."OpName" FROM "OpusInfo" "OpusInfo", "CompNameYOB" "CompNameYOB" WHERE ( "OpusInfo"."OpCom" = "CompNameYOB"."CompID" )
That last bit creates the join, I believe. In any case, save the edited query specification, close the "CombineTables" definition editing window, double click again on the "CombineTables" query. Hurrah! A sensible, if badly formatted, result! More on this in a moment, but first....
I am going to digress just for a moment....
If you have the same problems I had when getting started with queries, maybe the following will help.
The "bad" query we started with a moment ago showed every possible combination of elements from the first table with elements from the second table.
All queries in SQL (and ooBase is a SQL RDBMS) systems will, under the skin, be like this... it's just that they are usually useless, and so we rapidly get used to "weeding out" the duplicates, leaving us with a useful set of the combinations.
Consider for a moment two much simpler tables. Each has just two fields. The first table has three records in it. They hold 1/a,2/a, and 3/b. (I.e. a record with "1" and "a" in it's two fields, a record with "2" and "a", and a record with "3" and "b". The second table has just two records: a / XX and b/ YY.
An "un-weeded", basic, query, listing all of the fields from both tables, results in....
1/a/a/XX 1/a/b/YY 2/a/a/XX 2/a/b/YY 3/b/a/XX 3/b/b/YY
What is going on? Under the "skin"? I suspect that in practice, SQL manages to do it more elegantly, but it seems that it starts every query by creating a huge table, listing every possible combination of records from the first table with records from the second table. That's what you see in the list above.
But the list of all possible combinations is of little use.
What we need, although it isn't clear in the simple 1,2,3/ a,b / XX,YY example, is only a selection of the possible combinations of records from the tables. And from those combinations, we probably only want to see selected fields. If you look back to the music /composers example that this tutorial is built around, maybe you can see why I've gone off on this digression? The initial "bad" result was the "all combinations" (but showing only some fields) result.
Now bring your mind back to the 1,2,3/ a,b / XX,YY example. The last field in the first table is analogous to OpusInfo.OpCom. The first field in the second table is analogous to CompNameYOB.CompID
The "sensible", "weeded" result for the 1,2,3/ a,b / XX,YY example would be....
1/a/a/XX 2/a/a/XX 3/b/b/YY
It shows you the records from the "all possible combinations" table where the value from the second field of the first table matches the value from the first field of the second table.
Let me be more specific about how the two examples show the same thing...
You should treat "1" and "2" as the names of pieces. Treat "a" and "b" (in both tables) as the codes for composers. Treat "XX" and "YY" as their names, e.g. "Georg Friedrich Handel".
Get it? Never mind... you will! You can leave it for now, if it didn't "click". We're going back to "How to do it...."!
Before my digression, we had finally achieved a sensible result.... but it was set out in a rather "plain vanilla" way, and had other shortcoming. To get a good result, we now go onward and set up a report. The report will invoke the query, and present the information which was in the query result.
In the project manager window, change the task to "Reports".
Double-click on "Use Wizard to Create Report...", and wait a moment... your poor machine has a lot to do. Two things need to open. Wait for the "Report Wizard" window, with the steps you are going to work through listed down the left hand pane.
One of the strange.. but wonderful, when you get used to it, parts of Open Office is the integration of the elements. ooBase uses ooWriter for designing and displaying reports.
When you clicked on "Use Wizard to Create Report...", an ooWriter document came up, with some basic author information in the page's header. Not quite so quickly, but eventually, the report design window appeared, too.
In step 1, select Query: CombineTables and move all three of the available fields to "Fields in Report". (The >> "arrow" will do that for you in a single click.). Click "Next"
In step 2, you should take a moment to change the labels to something more human friendly, e.g. "Year of Birth", "Composer's name", "Name of piece". Click "Next" to move to step 3.
For this report, don't invoke any grouping. When you've run the tutorial as it says to run it, you might like to go back and investigate the result of asking for grouping by CompName. Click "Next" to move to step 5 (You can't sort reports of queries... the sorting for them is set up during the query definition.)
You can accept the step 5 defaults.
In step 6, make the name of the report "Music and its composers". Accept "Create report now".
Either now you should either just accept the suggested "Dynamic" report", or read the following aside. The tutorial continues in a moment.
Whichever you choose, you will fetch data from the tables as they stand now. If you choose a static report, if you run it again later, after changing data in the tables, the report will still only show the data as it was at the time you first created the report. If you've chosen to create a dynamic report, then each time you run the report, it will re-consult the tables, and present you with the information as it stands when the report is run. N.B.: If you happen to have the report open on your screen, and edit some of the data in the tables, the already-open report will not be updated. You would have to close it and re-run it to see the newly changed values from the tables.
If someone can point me towards the right tick box to change an already created report from "static" to "dynamic", I would be grateful.
Here ends the digression on static and dynamic.
The first result with your report may be quite unsatisfactory. The right data should be showing, but the way it is laid out may be poor.
Close the report.
Use the project manager to reopen it in edit mode.
Don't be alarmed that you see Latin gibberish where you expected to see data from the tables. Nor by the fact that there is only one line of data under the headings in the table.
With mouse drags, you can move the inter-column boundaries to better places, etc. Save your changes, close the document. Re-run the report from the project manager.
The first time I went through this material, the following was appropriate at this point. On a subsequent trip through the material it wasn't... but it still discusses a general "trick" which you may need from time to time, so I'm leaving it in....
If you find that a bunch of the right-click options have disappeared, I'm afraid you probably need to close everything, re-boot the computer. Boring, I know. I've just done it. Again. Perhaps you'll be grinding your teeth and saying "Now he tells me", but, I have to admit that while my report works.. usually... there does (at the moment) seem to be a little flakiness at this stage of the proceedings. Sometimes I have to do the restart already mentioned. Sometimes after a few "edit report" / "open report" cycle, Base just shuts down. When this happens, it often goes through a "repair" process during the next invocation. I suspect I am making requests which mess it up. IT DOES WORK, and work well, generally!! I suspect that the crashes I'm seeing arise from putting disallowed things into the report specification. I'd send reports of my crashes to Sun, but the crash report module is crashing, too... after I've struggled to write up the crash!
Anyway... Here ends the "aside"....back to our report....
I hope you've got the column widths arranged as you want them?
Try selecting all three of the column headings (just drag the mouse across them.) Change the typesize to 16, using any technique you would use in ordinary ooWriter word processing. It will accept that! Change the font style to bold. Change the font used, e.g. go to Arial. The report is just an "ordinary" ooWriter document.... even if it is using some features of ooWriter that you may not have encountered before. Anything you learn about working with ooWriter documents is immediately a new tool you can use in creating "ooBase" reports. This is an example of the power of the tight integration between the parts of OpenOffice. Enjoy!
I've written several tutorials with "relationships" (as in "relational database") at their core, and so have produced a sub-menu page for these related essays.
I dislike 'fancy' websites more concerned with a flashy appearance than for good content. For a pretty picture, I can go to an art gallery. Not everyone has fast broadband.
I present this material in a format aimed at to helping you USE it. There are two aspects to that: The way it is split up, and the way it is posted. Details at my page about how the material is split up and how it is posted.
Please remember the material is copyright. (TK Boyd, 2006 and later) The procedures in the page just cited are suggested only for convenient personal use of the material, however, also....
Feel free to use this information in computer courses, etc, but a credit of the source, quoting the URL, would be appreciated. If you simply copy the pages to other web pages you will do your readers a disservice: Your copies won't stay current. Far better to link to the original pages, and then your readers will see up-to-date versions. For those who care- thank you. I have posted a page with more information on what copyright waivers I extend, and suggestions for those who wish to put this material on CDs, etc. (There is at least one prison using the material for inmate education. Situations do exist where good internet connections are not possible!)
Translations are welcomed. Tell me about yours, so I can post links to it. (More information at the page about copyright waivers.)
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.
Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.org. Mostly passes. There were two "unknown attributes" in the Google+ button code. Sigh.
. . . . . P a g e . . . E n d s . . . . .