Page tested for compliance with INDUSTRY (not MS-only) standards, using the free, publicly accessible validator at validator.w3.orgMostly passes. There were two "unknown attributes" in 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, 3/06- 12/12.
I was playing about with these, and this tutorial relates what I have discovered so far.
I accidentally wrote another tutorial which covers much of what this covers. If you want more "hand holding", more "now type..." stuff, try the other tutorial. If in doubt, continue with the one you are reading.
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.
The following may seem like an awful lot of work... a bit of an elephant gun for the mouse in my sights. However, remember it is just to illustrate something... and that something entails important concepts. One quickly grasped advantage of doing this sort of thing the way I'm going to show you, the "hard" way, is that, for instance, if you mistype Bach's name, it will be obvious... every reference to Bach will be wrong, AND you only have to fix one entry to fix all of the manifestations of the error. Also, you can ESSENTIALLY (without actually) "type" "Wolfgang Amadeus Mozart" dozens of times, simply by typing just "wamo" those many times.
By the way... did you know that Mozart was also known as WG Mozart? More on this later, when you've puzzled on it.
Turning back to forms displaying data from multiple tables...
Imagine a world where every audio CD consisted of music by just one composer. (Many composers, but only one on any given CD.) Imagine also that each CD has a unique code, a bit like an ISBN, but much shorter!
Set up two tables:
First table: "CompNam" Composer's Names.
Fields:
CompNameID- 4 characters, text (Abbreviated name. Set as primary key) CompName- 20 characters. (Human friendly version of composer's name)
Sample data... but don't enter it into the computer before you are told to...
bach / JS Bach wamo / WA Mozart ..etc...2
Second table: "Albums".
Fields:
AlbumID- 8 characters, text (Set as primary key) ComposerByCode- 4 characters, text AlbumName- 30 chars, text.
(Returning to the diversion: WA/ WG Mozart? Figure it out? Hint: The G was for Gottlieb. Why two middle names?)
Back to work! There's just one tricky bit in the way we've built the tables and how we'll use some fields.....
Table 1 ("CompName") has the field named "CompNameID". In Table 1, each composer appears only once and the values in the CompNameID field are be unique, i.e. no two records will ever have the same value in the CompNameID field. That field is the table's primary key, so you will not be allowed to enter the same value in that field in two records.
Table 2 ("Albums") has the ComposerByCode field. This field should only ever have something in it which ALREADY appears in the first table's "CompNameID" field. I've given those fields different names in this example to make things easier, but in real databases, it would not be unusual for them to have the same name. A shorthand for The "CompNameID" field", in the first table, i.e. in "CompNam", is: CompNam.CompNameID: The table's name, a dot, the field's name. Sometimes you need to give the field name in full, but not often. It doesn't matter if you do use the full name, so if in doubt, use it!
As a specific example of something important, note that in table 2, "wamo", say, can appear as many times as you like. (Remember, however, that it can appear only once in the first table.)
(By the way: The right terminology for "The way we've built the tables" is "the database's schema". Two databases have the same schema if they have the same tables, and those tables have the same fields, and the corresponding fields all are all set to identical types. Learn the right terms for things, and you'll make much better progress with Google and manual's indexes!)
Second table sample data... don't enter it into the computer before you are told to...
Album ID/ Composer / Album name
bob1 / bach / Best of Johann Bach
bfc1 / bach / Bach Flute Concertos
mmot / wamo / Marvelous Mozart
mreq / wamo / Mozart Requiem
mvc / wamo / Violin Concertos by WA Mozart
... etc...
==============
So far so good? Now we come to a major part of the point of all this... a chance to demonstrate a relationship.
Before you put any sample data in those tables, tell the database about a relationship that you want enforced.
In the ooBase main project manager window's menu, click "Tools", and from the sub-menu "Relationships".
An empty "Relation Design" window should open. From its menu, "Insert"/"Add tables", and add both the database's tables to the Relation Design window.
Create a line between CompName.CompNameID and Albums.ComposerByCode as follows. (It is easy and intuitive, but half way through it, it may seem it "isn't working".)
That should give you a line between the two fields, with a "1" at the CompName.CompNameID end and an "n" at the Albums.ComposerByCode end.
If you have trouble creating the relationship, it may be that you didn't notice my warning about not putting data in the tables yet. You can put data in the tables before setting up the relationship... but if there are data which break the rules of the relationship, the "bad" data will block the creation of the relationship. Go back to the tables, edit out the problem data. Be sure to "commit" (save) the final data change, and try again to create the relationship. (You "commit" changes by moving the insertion point out of the record you just altered.)
Be sure to SAVE the relationship. It doesn't need a filename, as it is saved "inside" the database, along with all the database's tables, etc. But do save it, by using the "disk" icon on the toolbar, or invoking the main project manager window's "File | Save". If you try to close the window without saving, the system will help you... but if you leave the window open, go off to the next thing without saving, the new relationship will not yet be in effect across the database.
The "1" says that only one record in this table can have a specific value in the CompNameID field. The "n" at the other end of the line says that many records in the Albums table can have the same value in the ComposerByCode field.
The "only one record..." rule will be enforced by the database because CompName.CompNameID is a primary key on the table.
Why have you gone to all that bother?
An immediate benefit of all that is that you cannot now enter a value in Albums.ComposerByCode unless that value appears in a record in the CompName table.
This means that you have to do entries in CompName before using a new composer in the Albums table... but this is a very small price to pay for the "robustness" you have added to your database. You can still, say, enter "bach" when you should have entered wamo... but you cannot enter "bcah"... unless that's someone else's CompNameId. Good coding of your data can reduce the chance of such coincidences.
(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.
==============
Now enter the suggested sample data into your tables. You might want to experiment with a few deliberate mistakes, to see the relationship protecting you.
==============
Once you have set up those tables and the relationship, you can make a query draw just the things you need from the two of them. I'll show you how in a moment.
As you learn more about relational databases you'll continue to learn about tables, queries, forms and reports. It would be possible... not a good idea, as I hope I will explain for you... but possible to set up a single table like...
Album ID / Album name / Composer bob1 / Best of Johann Bach / JS Bach bfc1 / Bach Flute Concertos / JS Bach mmot / Marvelous Mozart / WA Mozart
When you see something "long" and flexible like "JS Bach" (it could be written like that, or "Johann Bach", "Bach, JS", "J.S. Bach", etc... especially if the same value appears more than once, start thinking... "two tables".
That doesn't mean we can't pull something like the above out of our database. After all...
Album ID / Album name / Composer bob1 / Best of Johann Bach / bach bfc1 / Bach Flute Concertos / bach mmot / Marvelous Mozart / wamo
... isn't going to be very satisfactory, is it? (Look at the last column. (And we can deal with the first one, too, if we wish.)
The answer is to use a query to, in essence, generate a "table". It will be a table in most respects... but will be a temporary table, a "scratch" table which will go almost as soon as it has been created. But not so quickly that we won't have a chance to use what the query has put into it. The temporary table can, for instance, and often is, used to fill the elements of a form. We'll see how, and why we would want that, further down the page.
The form displays what you need to know, via a "hidden under the hood" mechanism which is a better way of handling the data, in this case, in particular, the composer's names.
I singled them out for special handling because, without the special handling, the user would have to enter the same thing over and over... the composer's name in full. Admittedly, the user now has to enter the code for the composer over and over... but at least the code for the composer is shorter.
Also, if in the "Albums" table, you use the composer's name where I'm using my "code" inconsistencies would creep in: "JS Bach", "J.S. Bach", "Bach", etc. Computer-managed jobs thrive when inconsistencies are eliminated.
Remember: This is just an illustration, so I would not expect that you would necessarily do this job exactly this way.
===
So! How do we make the query and the form to accomplish what I show in the example?
After you've created the two tables, the relationship, and put the sample data in the tables, go to the main ooBase project management window. Select "Queries" in the left hand pane. Then click on "Create Query In Design View" from the Tasks pane. (You can also make this query with the Wizard, if you would rather... but I recommend, for most tasks, learning how to use the design window. Sometimes the wizard is helpful for making a quick, rough start... sometimes it is more hinderance than help. You need to go to the design tool eventually anyway, for "tweaking" work.)
A small "Add Table To Query" dialog should pop up listing the names of the tables available. Select "Albums", click Add. Select "CompNam", click Add. And now you can close the "Add Table To Query" dialog.
In the workspace of the Query Designer, you should now have a small box for each table, and in the box you should see the names of the table's fields. And the line indicating the relationship should be present. That line is very misleading! It is not just representing the relationship which you set up earlier. It comes from the relationship, it is true. But it is not "just" the relationship. More on this in a moment.
I WANT TO STRESS SOME THINGS.... Very shortly after being in the "crawling" stage of using databases, you should start to find yourself making databases with more than one table. As soon as you do, you should find yourself using RELATIONSHIPS. (They are described at length elsewhere in this tutorial, and discussed in others) SIMILAR BUT NOT THE SAME are "JOINS". You use joins when building queries (or reports) involving more than one table. I am a little new to joins myself, but am trying to build up the material in these tutorials about what joins are, how you use them. If you see "joins": Pay attention. And remember that even though they are somewhat "connected" to realtionships, they are not the same as relationships. Be careful when looking at a "think" to be right about whether it is a relationship or a join.
In the bottom of the Query Design window is something that looks a bit like a spreadsheet. The result of a query is always just another table: Zero or more lines, each a record, each made up of fields.
The "spreadsheet" at the bottom of the Query Design window is where you specify what fields are going to be in the table that results from the query. Each column of the "spreadsheet" is about one of the fields. Each row is one of the properties of that field's appearance in the result table. The field names go across the page, as they would in a table, but the rows below the names are not records in the database. Don't be fooled by the similarity between the two displays.
The first row specifies what field is going to be in the result table. Use the pull down to set the "Field" row of the first three columns to.....
Albums.AlbumID Albums.AlbumName CompNam.CompName
(As soon as you move away from the cell you are filling, the table name will disappear. It would remain if any ambiguity existed, e.g. if we had an Albums.CompID and a CompNam.CompID, which we might well have.)
That's pretty well all we need, but just to take care of a "frill": Fill in the Alias row with....
"Album ID" and "Album Name" and "Composer"
Those values will determine the displayed column headings.
Click File | Save (or use the button), and save the query as "ListAlbums"
You could double click on the "ListAlbums" entry in the ooBase main project management window at this point, even before you close the Query Designer. That "should" work. Long ago, I think that was behind some crashes I got. If you get crashes, try the "Run Query" button on the toolbar (green tick mark). There are various advantages to doing so while getting the query "just so", anyway, and you may prefer that method of running the query anyway. (It was a long time ago that I got the crashes. Much work has been done on ooBase since. I'll be interested to hear from you if you are getting crashes today... and are pretty sure you've done everything else properly!)
If you get every record twice, look to see in the query designer whether you have the line between CompNam.CompNameID and Albums.ComposerByCode.
If it is not there, create it, rather like you were supposed to create the relationship, which was similar, but is not the same as the query 's "join". Here, the line... which was probably created automatically for you... represents a join. Joins are important to queries.
One you have the line, signifying the join, you can right click on it, select edit, and learn some things about your join. For now, do not tick the "natural" box. Note that the join is a "left join"... which is all you need to know about for the moment.
Once it is working properly, by all means invoke the query by double clicking on the "ListAlbums" entry in the ooBase main project management window. Having the Query Designer window closed may be part of the crash avoidance procedure.
So! That gives us a pretty good way to see the data in our tables in a human friendly way. However, for just a little more work, we can get an even better result.
Close the Query Designer. On the ooBase main project management window, in the left hand pane ("Database"), select "Reports". In the newly changed Tasks pane, click on "Use Wizard to Create Report".
Step 1: Field Selection: Select "Query: ListAlbums
--- Use the >> button to move all of the fields in the query from the "Available fields" to the "Fields in Report" memos. Click Next.
Step 2: Labeling. You can leave things as they are, so just click Next.
Step 3: Grouping. You can leave things as they are, so just click Next.
Step 4: Sort Options: You can have the report sorted by Composer, or Album, or AlbumID... you choose! And then click Next.
Step 5: Layout. Again, you can leave things as they are, so just click Next. (You might want to specify "Portrait" layout.)
Step 6: Create Report. You can leave things as they are, so just click Finish.
You should get a special ooWriter document, with the information you expect!
An aside: Just now, I didn't get what I hope you will. I got something close, but with just one line, and that was pseudo-Latin gibberish. Why did this happen? I'm not sure. After a bit of "wiggling things", I DID get the right result. Nothing extreme. I didn't restart Windows, or even ooBase. I went off and looked to see if a Java RTE was in place. (Main project manager window menu "Tools"/"Options"/"OpenOffice.org"/"Java" (1.7.0_07)). I tried doing a report on just a single table. That worked first time. Ran the query a few times. Worked fine. After both of those, I remade my report- from- query again... and this time it worked. If anyone can spot why it DIDN'T work the first time, I'd be delighted to know. (The little "whoopsie" arose at the end of a long editing session, and was distinctly unwelcome, I can tell you!) (The ooBase was in a 3.4.1 OO, recently installed on a Windows 7 machine.)
Sometimes the "stuff in Latin" result arises because although the report is "okay", there's some problem with the connection between the report, and the data is it meant to be presenting. Maybe a field's name was changed in the table's definition AFTER the report was set up? Etc. It may be that if a database isn't "registered" you will get the "Latin" result. Not sure.
End of aside... getting back to using our report...
If you close it, and go back to the ooBase main project management window and right click on the report's name, and select the "Edit" option, you'll open ooWriter again, but this time with the design of your report loaded. In that, you can adjust column widths and do all manner of things. Save it. Close it. Re-run the report. Happy? You should be! Imagine doing a CD inventory project for real, and being able to add/ remove/ edit records, and then re-print a neat, tidy, clear list of the CDs in the collection so easily. Care to imagine doing the job another way? (You can't, by the way, as far as I am aware, add fields to the report. Go back. Re-do things from the start, if you want to make that sort of change to the report.)
===================
The Report Wizard is far from perfect, but it does do a reasonable job of everyday things. However, don't wait long before starting to use the Report Builder. (Once called "the Sun Report Builder". No doubt a new name this week. "Oracle Report Builder"? "Apache Report Builder"?) It is fabulous, by whatever name. It is also the recommendation of my "elders and betters". I've done an introduction to installing and using it.
I hope the above has been useful......
Concluding the diversion: Gottlieb? Amadeus? Both say "beloved of God." One in Wolfie's native tongue, one in Latin, close to the Italian, in which it is Amadeo, which, so I'm told, he also used in his most prolific correspondence. He even used a French form: Amédé! (That should be Amede with acute accents on the e's.) Italy was in vogue as a source of good music, culture, etc. (My thanks to an OpenOffice.Org Forum participant who corrected an inaccurate earlier version of this diversion!)
A detail: In our example, the composer code in the composers table was in the field of that table's primary key. When you want to consult a table by a connection through something like the composer code, that datum will often be in the table's primary key. If it isn't, I suspect that the field may need to be indexed... a story for another time!
===
Nota Bene: A report is for looking at your data. If you want to edit what is in the database, you use a form. Elsewhere, I address setting up one form to work on data from multiple tables.
I accidentally wrote another tutorial covering almost the same ground as the one you are reading now. The one you are reading is probably better, but consider reading the other, just to double check your grasp of the material? It also ends slightly differently.
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.
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.orgMostly passes. There were two "unknown attributes" in Google+ button code. Sigh.
. . . . . P a g e . . . E n d s . . . . .