A Guide to OpenOffice Database by a Non-Techie
OpenOffice Database, also known as OOo Base, is a free, open-source database that you can download from OpenOffice.org. This guide was written by a non-techie, so please understand that some information might be flawed. This is by far not exhaustive but only meant to be an introduction that might help others with the speed bumps I found when trying to learn OpenOffice. I hope this guide will prove useful to another beginner.
- Creating a Flat File
- Creating Relationships
- Adding Data/Creating a Form
- Modifying a Form
- Creating a Query
- Quick Search
- Helpful Links
Creating A Flat File
A flat file is a file that contains one table. The fields of this table can be filled with information you want to group together, such as Customer Names, Phone Numbers, and Email Addresses. Though the wizard can be used to create a table, I found that I prefer creating my own. To create a database without the wizard, when you first start a new database, uncheck the box that says “Open the database for editing” and click on “Create table in design view...” after the database opens.
The fields you wish to include in your table are entered in the left column and the field type will be selected to the right of that. One field should be designated as your Primary Key. A Primary Key is a field that distinguishes one record from another. It can be numbers, letters, or a combination of the two, but it must be unique. For example, if you were creating a table to keep track of your customers' information, a name would not be a suitable Primary Key because one customer may have the same name as another. You would want to create a unique customer ID for each individual.
In the example below, I chose “Integer” as my field type for “CustomerID”. I did this so I could choose “Yes” for the “AutoValue” option at the bottom of the window. By choosing Auto Value, not only have I saved myself time, because the database will supply a number for me automatically, but I have made sure that I will not have the same number for any of my records because Auto Value will use the next consecutive number for each new record. I then right-clicked the area to the left of the “CustomerID” field and chose “Primary Key” from the menu.
Link: Flat file tutorial http://www.youtube.com/watch?v=63iZnw0S6tE
So, I have created a customer table and if that is all I wanted and needed then I would be done. I could now add my data by left-clicking the table from the main screen or creating a form. I could search through the data with a query or build a report. But what if I wanted to also create another table for the products I sell and then link which customer bought what when? I would want to create two more tables, one to catalog my products and another to catalog my orders.
My Product Table consists of ProductID (Integer), ProductName (Text), and ProductPrice (Number). By selecting "Number" for the field type for ProductPrice, I can add some decimal space.
The Order Table will be my bridge between the Product Table and Customer Table. It contains OrderID (Integer), Date (Date), CustomerID (Integer), ProductID (Integer), and Quantity (Integer). I want to keep the field types for both Customer and Product the same as they are on the other two tables; in this case, they are integers.
Now that I have finished creating my other two tables, I need to create my relationships. Go to your main window and look at the toolbar at the top. Find "Tools" and click on "Relationships". You will be prompted upon entering which tables you would like to add. Add any tables that you are linking together.
I have added my Customer, Product, and Order Tables. To link my CustomerID from Customer Table to my Order Table, I clicked on the CustomerID in the Customer Table and dragged it to the CustomerID in the Order Table. A line should form between them. I did the same for ProductID. This should create a link between these tables.
Link: http://www.youtube.com/watch?v=h770gCE2OyU (though this tutorial is in Italian, I would recommend watching it if you find yourself stuck.
To add your data, you can open your table from the main menu or you can create a form. I have added some data to my Order Table to the right by going to the main window and left clicking my table. The numbers typed in for CustomerID and ProductID were the corresponding numbers of the customers and the products they bought. For example, if John's customer ID was 5 and he bought lollipops with the product ID of 2, then I typed in 5 for Customer and 2 for Product. Note: Your AutoValue will start your records at "0", but you can change it to "1" once you have typed in the information for one record.
Simple Form Creation:
When creating a form, I recommend using the wizard. You can find the wizard by clicking the "Form" tab from the first window and looking at your options up top. Once in the wizard, you can choose which table you want to work from. To create my simple form, I have chosen my Customer Table. You can select fields one-by-one to move to your form or you choose all of your fields by selecting the ">>" button.
I am going to keep my form simple and skip the subform. Information for subforms can be found at the link section below. Instead, I am going to skip down to "Arrange Controls" and choose the template on the left and then skip to "Apply Styles" to find a color I like. The very last tab, "Set Name", will give me the option to modify my form. You can skip this if you are not doing anything special to your form. I selected this so I can add a combo box to one of my fields.
Subform - http://www.linuxtopia.org
Creating a Combo Box
Creating a Combo Box allows you to create a field that has a drop down list from which you can choose an entry or enter your own. In this example, I created one for my "State" field but you could find many different reasons to create one of your own. You can edit your form by right clicking the form from the form tab on the first window or selecting the "Modify Form" in the last step of the wizard.
1. Right-click the field you want to modify and select "Group". Choose "Ungroup" in this menu. Ungroup will separate the text, in this case "State", from your field box. Note: Left-click anywhere on your screen before continuing to the next step.
2. Right-click on the field box. You should be able to find a "Replace With" under "Control" and "Form". If "Replace With" is not there, click the screen somewhere away from the field box and try again. You can choose many different options here but I am choosing a "Combo Box". A Combo Box will allow me to type or to select something I have typed in the past.
3. Double left-click on the field box. Scroll down the General Tab until you find the "Dropdown" option. Change it to yes.
4. While this menu is still open, switch to the Data Tab. Change "Type of Input Required" to "SQL" and click on the "..." button found to the right of "List Content".
5. I added the Customer Table and double-clicked the "State" field. This will add the "State" field to my bottom window. I changed my "Sort" option to "Ascending" so my entries will remain alphabetical. Save and exit.
When you are done modifying your form, exit out. You can now use your form to enter your data.
Link: http://www.youtube.com/watch?feature=endscreen&NR=1&v=XmyeSxHe44Y (Italian, but useful)
After filling in data for my Customer, Order, and Product Tables, I can now use a Query to search my data for particular information, define criterion to parse out specific records, or even multiply or sum totals. I will not be using the Wizard because I find, by using "Create Query in Design View", I have much more control over what I can do with the data I have.
Creating a Query
Using Criterion to Parse Out Data
1. Click on "Create Query in Design View.." and add your tables. From the tables, double-click the fields that you would like to include in your query. In my example, I wanted to find out which of my customers had the most expensive sweet tooth. To find out, I have added "FirstName", "LastName", "ProductName", and "ProductPrice".
2. Find the "Criterion" row in the bottom window and type whatever value you wish the query to retrieve. I wanted my query to retrieve who bought products that cost more than $2.00. To do this, I typed "> 2.00" (without quotes).
3. Find the "Run Query" button in the toolbar and give it a test run. If the data retrieved is what you wanted your query to produce, then save and exit. You can open this query any time in the future and it will take into account any new records that you have added since its creation.
Create Query in Design View
In this example, I want to extract certain fields that are linked, such as the names of my customers, the products they bought, and the date they purchased the items, and I would also like to create a new field that will tell me how much money they spent.
1. First, I clicked on "Create Query in Design View" and added my three tables. From these tables, I added "Firstname", "LastName", "ProductName", "ProductPrice", and "Quantity".
2. I decided I did not want the "ProductPrice" to be visible in my query, so I found the "Visibility" row beneath "ProductPrice" in the bottom window and unchecked the box.
3. I want my query to calculate how much my customers spent with each purchase. To do that I typed " "ProductPrice" * "Quantity" " (remove the outside quotes) in the top row of an empty field. Beneath that I can type in how I want that field name to appear, in this case I typed "Total".
4. I can check my query to see if everything turned out the way it should have by finding the "Run Query" button in the toolbar. Save query
You can do a quick search on your tables or queries by using the "AutoFilter" button or the "Standard Filter" button found in the toolbar when you have either your table or query opened. To use the AutoFilter, click on the text you want to filter, such as "Cookies", and then hit the button. All records that do not have cookies in the ProductName will be removed. You can alphabetize your results.
You can also use the Standard Filter button to filter your records. You might choose to exclude records that have a particular value or you could include several values that you want to retrieve.
- Download - http://www.openoffice.org/
- Forum - http://www.oooforum.org/forum/viewforum.phtml?f=10
- OpenOffice User Guide - http://www.openoffice.org/documentation/manuals
- Tutorial - http://sheepdogguides.com/fdb/fdb1main.htm
- Tutorial - http://www.pitonyak.org/database/
- Tutorial - http://www.euclideanspace.com
- Flat file - http://www.youtube.com/watch?v=63iZnw0S6tE
- Relationships - http://sheepdogguides.com/fdb/fdb1relone.htm
- Relationships - http://www.youtube.com (Parte 1) (In Italian, but worth watching)
- Form - http://www.youtube.com (Parte 2) (See above)
- Subform - http://www.linuxtopia.org
- Combining and Summing - http://openoffice.blogs.com