I like to tell this joke in my OpenOffice.org classes. A helicopter pilot is taking his buddy up for a ride, flying around Seattle, and the fog rolls in. They get lost. Finally, the cloud parts and they see someone standing on a rooftop. They yell down to him, "Hey! Where are we?" The rooftop guy responds, "You're in a helicopter!"
The helicopter pilot laughs and says, "I know exactly where we are. We're above the Microsoft campus in Redmond, where they write the online help." His buddy says "Huh? How did you know that?" And the pilot replies, "The information was absolutely accurate and not at all helpful."
The trouble with what the rooftop guy told them was that it was just data. Data alone is -- well, it's a good starting point but it's not really that useful on its own. These numbers alone, for instance, aren't going to get you anywhere near an insightful report or a brilliant five-year business plan.
What you would like to get from your data is information -- data that's been interpreted a bit. Something that tells you, for instance, the average amount that customers spend when they come into your store, shown here.
Getting this kind of information isn't extremely difficult, but you need to understand how it works, as well as know how to use the software to make it work.
Understanding how to use functions
Let's say you've got this kind of data:
- A bunch of books: titles, prices, etc.
- A bunch of customers: names, addresses, etc.
- A bunch of invoices that combine customers and books
Sometimes you'll just want to print out or view a list of your customers or a list of your books or just a list of the invoices. But sometimes you'll want this kind of data:
- A list of each of your invoices and the number of items on each invoice
- A list of each of your invoices and the total of the amount on each invoice
- A list of invoice IDs, each of your customers and the average amount they spent
To get the count, or the total, or other information, you use functions. They're available in the design view (shown), the wizard, and of course if you know SQL, then you already know how to use them.
The key thing about using these functions is that they're for one-to-many relationships. One-to-many relationships are like an invoice and the items on it, or a customer and her invoices. Here's an example of one-to-many data.
InvoiceID ItemNumber IJKK-6 34-12 IJKK-6 8-222 IJKK-6 8-344 IJKK-6 92-L88 IF12-12 99X-12 IJ12-X 912-4 IJ12-X 312-X
To use functions on this data, you assign the function to the many item; in this case, the item number. Here's an example of what the data would look like.
InvoiceID ItemNumber (Count) IJKK-6 4 IF12-12 1 IJ12-X 2
There's one more thing you need to do, however. To make it blindingly clear to the database program that this is for a one-to-many relationship, that this is a special sort of relationship between the fields, you need to assign a function called group to the one field, too. In this case, the one field is the invoice ID.
Conceptually, here's how you would actually set it up.
InvoiceID (Group) ItemNumber (Count) IJKK-6 4 IF12-12 1 IJ12-X 2
Creating a simple view that uses functions
I'm using a database containing three tables: Customers, Books and Invoices. The Invoices table is doing the real heavy lifting, so I'll focus on that for these examples. I'm also ignoring the big area of relations, since that really isn't what's important when you're using functions.
The key thing is one-to-many relationships. The invoice table is chock full of that. For every customer, there can be multiple invoices. For every invoice, there can be multiple items. There are many one-to-many relationships for which you might want to do calculations.
Let's say you want to start off with something basic: create a view that lists the invoice IDs and the total amount spent on each invoice.
1. Open the database file.
2. Click the Tables icon on the left side of the work area. books
3. Click Create View.
4. Select the table containing the relevant data and click Add.
5. Double-click the one field (InvoiceID here), then double-click the many field (Price here).
4. In the Function row, select Group for the one field, and Total for the many field.
5. Click the Run Query icon.
6. You'll see the results.
7. Close the view, saving results, and name it.
As you can see, even though the original field Price was a decimal with decimal places, the function result based on it isn't. You can double-click the view and change this.
1. Right-click on the column heading and choose Column Format.
2. Select the format you want and click OK.
3. The display will change.
Creating a marginally more complicated query
Let's say you wanted results like these: for every invoice and customer combination, the total amount that that customer spent.
When you have two or more one fields, you need to assign the Group function to all of them. Here's how you would set up the view or query to get the data in the illustration above. Even though the InvoiceID field is the main one that groups the records together, the CustomerID field plays a role too, and you need to assign the Group function to it as well.
Notes on functions and the wizard
The principles are the same when you use the wizard to create a query. But in practice, you can't get the one field. That is, you can list the number of items on an invoice, or the total of the prices on an invoice, but you only get the calculated field. You don't get the invoice ID, the one field, so the query doesn't make any sense.
Here's what it looks like.
Not really that illuminating or useful, eh? So you're much better off doing the design view or, if you're a SQL wiz, just go with what comes naturally.
Getting all the information you can
Functions are a good way to upgrade your data into something useful. Include restrictions and sorting in your queries and views, as well. Reports are another good way to make sense of your data, especially when there are one-to-many relationships.
I also want to put in a plug for the OpenOffice.org online help—there's good overview and reference information, and it absolutely beats going up in a helicopter and asking a guy on a rooftop.
What other OpenOffice tips would you like to see from Solveig? Email us and let us know.
Solveig Haugland has worked as an instructor, course developer, author and technical writer in the high-tech industry for 15 years, for employers including Microsoft Great Plains, Sun Microsystems,and BEA. Currently, Solveig is a StarOffice and OpenOffice.org instructor, author, and freelance technical writer. She is also co-author, with Floyd Jones, of three books: Staroffice 5.2 Companion, Staroffice 6.0 Office Suite Companion and OpenOffice.Org 1.0 Resource Kit, published by Prentice Hall PTR. Her fourth book, on OpenOffice.org 2.0, is coming this summer. For more tips on working in OpenOffice, visit Solveig's OpenOffice blog.
Add your voice! Click the following links if you'd like to vote for the features that would make exporting and importing data easier.
Vote for adding a wizard to import data into Base:
Utility to export CSV from Base:
This was first published in November 2006