AUTHOR'S MAIN SITE  > > > > >  TABLE OF CONTENTS for Open Office database tutorials.
Delicious.Com Bookmark this on Delicious     StumbleUpon.Com Recommend toStumbleUpon

Open Office 2 Base (database) Tutorials
"Cross populating" (my term) forms
Drawing on multiple tables

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, 12/12.

Data from more than one table _______________

This page is not of my usual style. There is little "now type this" material here. It is more "question" than answer... for now. But I think it will be useful for some advice it contains.

As soon as you go beyond the earliest "crawling" stage in using Open Office Base, or any other relational database... there's a hint in the name... you will begin using more than one table in your database. (Beginners sometimes think that a table is "a database". While some very, very simple databases only have one table, they are very limited. Even if they can be useful.

Once you have more than one table, be sure to master the basics of relationships. (I have pages about them elsewhere.

When you have more than one table...

Be careful that you don't enter anything twice. This rule is part of what we call "normalizing" the database. Lots of good stuff on that around. Try "database normal form" in Google. Wikipedia has a good article.

Let's say you have a database for signing books out of a public lending library. A very simple database! (Also used in my essay on joins and relationships!)

It could rely on each book having a unique ID printed in the book. It, the book's title, etc, etc, might of course also be in the computer... but for our purposes elsewhere it didn't need to be. For this essay on "cross populating forms", it will be a help. We'll call that "Books", and it will have one field for the ID, one for the title, one for the author.

Note that the ISBN alone won't do. Each book in the library must have a unique ID. That could START with the publisher-assigned ISBN... but then the library would have to add something like -1, -2,-3... to distinguish multiple copies of that book, if the library had them.

Second table...

There would also table with the details of the people with permission to borrow from the library... their names, addresses, phone numbers, etc. AND a unique- to- each- person "ID code". We'll call the table "Customers", and the customer ID "CustID".

Third table...

The only other table we need is a "BooksOutOnLoan" table. In a SIMPLE lending database, we would need only two fields: Book and Borrower.

BooksOutOnLoan.Book would be the table's primary key... a given book can only (in our alternative universe) be on loan to one person at a time, and we aren't going to try to track lending history. The other field will have a person's ID code entered in it.

If four books were on loan, the BooksOutOnLoan table might... if you had designed your ID coding very badly.. look like...

     Book    Borrower
    BK003    Pers528
    BK106    Pers528
    BK007    Pers312
    BK823    Pers492

I hope you see that the above "says" that one person (Pers528) borrowed two books, and two other people each borrowed one book. If you don't see that, struggle with it until you do, because it is important to things to come.

So far, this essay has a lot of overlap with the one about joins and relationships. At this point, they radically diverge.

You should have the "obvious" relationships in place... but if they aren't "obvious" to you, you can study the joins and relationships tutorial later.

Don't enter data twice

Finally the stage is set.

In the context of all of the above, consider this: Would it be a good idea to list more than just the book's ID in each record in the BooksOutOnLoan table? What about listing the name, for instance?

No! No! No! No! No!.... THAT would be exactly what I meant about "entering data twice".

The book's name would appear BOTH in the Books table, and in the BooksOutOnLoan table. Bad Idea. YES: The "BookID" is going to appear more than once. But, as mother would say, that's a special exception. Allowed Because I Say So.

I say so, because it is necessary. BookID values are being used to link records in one table to those in another. I hope you see that the use is special? If you don't, yet, hang in there, and you will eventually "get a feel" for it.

All is not lost

Even if you DON'T "enter data twice", you CAN still pull values from multiple tables together in a form.

Let's say you wanted to print out cards to send to people, telling them what books they had out? (Yes... you'd want to make the whole thing fancier, record WHEN book was borrowed, only print out long borrowed books, etc... but hey!...)

The table those cards would be based on only has...

     Book    Borrower
    BK003    Pers528
    BK106    Pers528
    BK007    Pers312
    BK823    Pers492

NOT very useful... directly!

But... and here's where, for now, I am going to let you down by not saying how... it would be quite possible to have the database look up the "human friendly" name of the borrower, and the human friendly name of the book, and substitute those in the printout.

Also, I displayed the contents of the table as a grid a moment ago. Again: The database is quite capable of producing a series of cards each of which would look something like...

Dear NAME,

We wanted to let you know that our records
show that BOOK has been signed out to you,
and unless there's been some mistake in our
record keeping, you still have it. Please
get in touch?

As I said... it could print a series of cards, with a person's name in place of "NAME" above, and the book (or books) borrowed in place of "BOOK".

Take another example:

At the desk where people check out and return books, the operator would enter the person's ID, perhaps from a borrower's card, and the book's ID, from markings on the book.

This could be done onto a form... using that term both in the narrow database sense, and in the sense it is used in the wider world. The form would be an electronic one, on the computer screen.

The operator would enter codes, because they are short and unique. But... as a check on various things... as soon as the operator entered a person's code, their details would appear in other boxes on the form. Ditto the book.

That's all for now...

Sorry folks... that's all I'm writing on this for now. I hope it was useful. I hope it gives you the confidence to press ahead to whatever goal you are working on at the moment.

I hope the "don't enter data twice" rule is more clear to you that when you started... it is an important one.

Editorial Philosophy

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

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.orgMostly passes. There were two "unknown attributes" in Google+ button code. Sigh.

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

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