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

Forget any experiences you may have had with Adabas, which came with Star Office, the commercial version of Open Office 1. The Open Office Version 2 database, "Base", aka "ooBase", is unrelated. And remember that Open Office, including Base, is free! But don't let that fool you. It's not new. Big organizations, civil and governmental, are adopting it as their standard office suite... and saving million$.

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.

Page contents © TK Boyd, Sheepdog Software ®, 3/06.

Multi- table queries and forms _______________

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.

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 entail important concepts. One quickly grasped advantage of doing this sort of thing this way, 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" many those dozens of 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:

bach / JS Bach
wamo / WA Mozart
..etc...

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. Because that field is a primary key, 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 ALSO 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 the first table's CompNameID field" is: CompNam.CompNameID: The table's name, a dot, the field's name. Sometimes you need to give the name in full, but not often. It doesn't matter if you use the full name, so if in doubt, do!

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....

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...

==============

Once you have set up those tables, you can make a query draw just the things you need from the two of them. I'll show you how in a moment. If you get every record twice, as you may well do at first, the extra thing you need to do (you do need to do it... it's just that you may have done it "automatically") is to declare that there is a relationship, a link, between the following fields:

CompNam.CompNameID
Albums.ComposerByCode

N.B.: There are different ways to set up relationships, and relationships can be set up at different levels. Again, I'll show you what you need to do. But first we're going to think a bit about the schema. It might not seem the best way to go, at first, but I hope I can show you its virtues.

Our data is stored in two tables. There are advantages to this, as I hope you will eventually see. But those tables need to be linked, and that's what the CompNam.CompNameID and Albums.ComposerByCode fields do for us. In order to link two fields (from separate tables), it is essential that the field types, including the length specification, of the two fields be identical. (You would guess that, if you thought about it.)

As you learn more about relational databases you'll continue to learn about tables, queries, forms and reports. A query can be the basis of a form, and a form could display....

Album ID / Album name / Composer

bob1 / Best of Johann Bach / JS Bach
bfc1 / Bach Flute Concertos / JS Bach
mmot / Marvelous Mozart / WA Mozart

The form displays what you need to know, but "hidden under the hood" 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 entering the same thing over and over... the composer's name in full. Admittedly, I now have to enter the code for the composer over and over... but what I have to enter is more concise. Also, if you use the composer's name where I'm using my "code" to link a record from the "Albums" table to the relevant record in the other table, inconsistencies would creep in: "JS Bach", "J.S. Bach", "Bach", etc. Computer-managed jobs thrive on the elimination of inconsistencies!

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, and put the sample data in them, 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.

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.

Put your mouse pointer on CompNam.CompNameID, press and keep down the left mouse button, move the pointer to Albums.ComposerByCode. You should see a fine line between the boxes now. It may be useful to drag one of the boxes to a new position to see the line more clearly. It is saying that a relationship, for this query, has been established between the two fields.

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 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... but I think it is behind some crashes I've had, so don't. There's a "Run Query" button on the toolbar (green tick mark). Use that. There are various advantages to doing so while getting the query "just so". 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... but be sure first that the Query Designer is closed.

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 Next.

You'll get a special ooWriter document!

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?

===================

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 Sun's Report Builder. It is fabulous, I think.... on the basis of 3 hours' "play"... but 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!)

Some Odds and ends__________

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 link 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 needs to be indexed... a story for another time!

===

A frustration: I've not found a way to set up a similar, data-from-more-than-one-table form that allows me to edit data in the underlying tables. I am using ooBase version 2.0 From discussions at the OpenOffice.Org Forum I gather that this is something that may be possible in newer versions of ooBase.

===

Relations, i.e. links between fields, may be part of the solution to creating forms which let you edit data in related tables. If you look in the ooBase help file under "Relations- Creating and Deleting", you get some information. In an older version of ooBase 2.x, I found the "Edit- Database- Advanced" option for my ooBase-native database... but the option was grayed out, not available. (In ooBase 2.4, there's no mention of relations in the same place.) Is ooBase not fully a relational database? (Shock, horror!) (I don't think so, by the way.... I think this is just one of those things I haven't mastered.) In the notes above, we "create a relationship", but that appears to be just related to the query. (You can learn more by looking at the underlying SQL code.) What I was thwarted from doing was creating relationships between tables at a more fundamental level... and the help file does suggest that not all database engines are capable of this. Remember that when you work with ooBase, you are mostly working with a front end which can communicate with many databases. My tutorials concentrate on using it to communicate with the database engine which is supplied with OpenOffice version 2, the derivative of HSQL. I'll be disappointed if that can't have relationships between tables... but if someone knows I can't have them... please let me know?!

I did discover that I could do something quite like creating a relationship, though not by the method suggested above.

If you go to the main project management window and click on the menu item "Tools", and then choose the first item, :"Relationships", you will open the "Relation Design" window. Specify the tables you want to link, "Albums" and "CompNam". A box for each, filled with the table's fields, will appear. Drag ComposerByCode from the Albums table to CompName of the CompNam table. When you finish, there should be a line between the two the now linked, related, fields. There should be a "1" at the CompNam.CompName end, and an "n" at the other. This signifies that the relationship is a "1 to many", i.e. there is only one instance of a given value in CompNam.CompNameID, but that there may be 1,2,3... 300 records in Albums with that value in the ComposerByCode field. Close the Relation Design window, agreeing to save the work you've done if you are asked!

That seems to define a relationship. I think it will help you enter correct data, but you may have to do something to "turn that on". You (may?) still also need to declare the relationship if you were now to build the query we discussed earlier.

Good news: In a different attempt to write this tutorial, I tried to create a link between two tables... and it seemed to work. I may have been using a slightly different version of ooBase.

As mentioned a moment ago, 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, using a report.

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.

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

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