Login
Password

Forgot your password?

LibreOffice Base Basics

By Edited Nov 13, 2013 1 0

LibreOffice Base
Are databases central to what you do for a living? If so, you probably wish you had a viable, cheap alternative to Microsoft Access or SQL. Nothing against those guys, but you're sick of paying for Microsoft Office and an SQL injection attack is not fun for anybody who happens to be in charge of maintaining databases. LibreOffice Base is a good option for people who are familiar with Access and like the idea of finding a free version. If you're not so much and are leery about changing from your usual database program, maybe an overview of the basics will help.

Creating Your Database

Create A Database

When you open LibreOffice Base, you should see a dialogue box that looks like the one above. For the first screen, you can either take the defaults to create a new database and hit “Next,” or open a database that already exists. For the second screen, you have the option of registering the database with LibreOffice. I usually don't, but that's just my preference. To continue, hit “Finish”.

Create Tables

Create Your Tables

For this one, you have a couple of options. You can create a table in Design View, or you can use the wizard to create a table. I prefer creating the table in Design View for better customization options. In this view, you name your fields in the first column. In the second column, you choose the data type from a drop-down list. Make sure you choose carefully, because any programmer knows that a Double data type is going to be treated differently from an Integer data type. They're both numbers, but Integers are whole numbers and Double allows for numbers like “3.14” or “4.5”.

While creating your table, plug a short description into the Description field. It should be a precise description of what data goes into any particular field.

While creating each field, use the boxes on the bottom of the dialogue box to determine whether the field must contain a value, the allowed width, the default value, and the format example. Why use these? Well, it might look a little strange if you're keeping track of your expenses and suddenly you notice that $1000 seems to have not gone anywhere in particular. Besides, you don't expect data entry clerks to write a book using Base, so you can manage how much data goes into each field to control your memory overhead.

When finished creating fields but just before you save it, choose the field you want to make the primary key. Right-click on the gray box to the left of the row and select “Primary Key.” The primary key must have a unique value for each entry in the table. If you hit “Save” before doing this, it will ask if you want to create one. If you hit “Yes,” it creates a field called “ID” to serve as the primary key. The primary key creates a way to directly access entries in a table so that you can move between entries that have an ID of 1, 500, and 10,000 without having to scroll through many entries.

Edit Tables

Edit Tables

Your new table should now be visible in the “Tables” area of Base's main screen. Double-click on it to open it for editing. Keep in mind the data type you selected for each field when you created it. When you start typing on the first entry, it will automatically add a blank entry just below it. In fact, actually editing your actual table works a lot like you would do with LibreOffice Calc. It tends to be a little stubborn about manually inserting rows, but you can delete a row by right-clicking to the left of the row you want to delete by and clicking “Delete Row”. Right-clicking on each column and row also gives you options for adjusting the row height and column width.

Queries

Queries

Queries are the best way to find related information in your tables quickly. To create a query, click on “Query”, and then you can select “Create Query in Design View,” “Use Wizard to Create Query,” or “Create Query in SQL View.”

Design View

Design View for queries

In Design View, select the table(s) that you want to query, and then close that box. Then select options for:

  • Field: The “category” you want to search in and/or display. If you are searching in more than one table, it might look something like Table.ID, with Table being (obviously) the table and ID being the Field you selected.

  • Alias: What you want to call the field in your query. For the sake of simplicity, I use the same name as the “Field” name.

  • Table: Should already be filled in if you selected a field.

  • Sort: Ascending or Descending.

  • Visible: Check the box if you want it to be visible. Leave it blank if you just want to query the field without displaying it.

  • Function: Can choose from a variety of options that calculates for “Average,” “Maximum”, “Minimum,” “Count”, “Sum”, etc. Useful if you want to show trends in your data.

  • Criterion: Leave blank if you don't want to narrow down the results in that particular field. Entering >500 will return any record that is greater than 500. Keep in mind that this function will return 501 but not 500, so if you want it to return results with 500, use >=500. Entering <500 will give you results that are less than 500. Below the Criterion field is a few options for “Or” statements; by using them, you can tell the query function to, for example, “Return records that are >500 OR =0”.

  • Run the Query: Once you have selected the fields, options and criteria you want, run the query by hitting F5.

Create Query Wizard

Wizard for queries

Fairly straightforward if you want a step-by-step process for creating your queries. To use the wizard, just select the options you want in the appropriate screens:

  1. Table and Fields: Select the table you want to choose fields from, and then select each field you want from the Available Fields box and use the Greater Than (>) button to move them over to the Fields in the Query box. The >> button moves all the fields in a particular table over. If you added one too many, just remove it by selecting it in the “Fields in the Query” box and hit the Less Than (<) button.

  2. Sorting Order: Choose the field you want your query to use as the first criterion for sorting and choose whether you want it in Ascending or Descending order. You can also set it to sort results in more fields as secondary criterion once you have your primary criterion set. Do not skip this step if you're querying a large database, because it is very useful for finding specific data quickly.

  3. Search Conditions: What conditions do you want it to meet? You can set this option to find records with all of your criterion (“Find records with a Last Name of Johnson AND with an Income of more than $25,000”), or you can set it to find records that match one or more of the criterion you set (“Find a record with Full Name of Pam Anderson OR a Full Name of Keisha Anderson”).

  4. Detailed or Aggregate Summary: “Detailed Summary” shows all the records that meet the set criteria. “Aggregate Summary” can be set to show the sum, average, minimum or maximum of the selected records.

  5. Alias: An easy-to-understand name for each field selected for the query. For simplicity, I just use the original names for each field.

  6. Overview: Review the options you selected for the query. You can go back or make adjustments, or hit “Finish” if you are satisfied with what you see.

Create Forms

Database Forms

If you run a website in which you ask users to input data or need an easy way to enter data on your own for any reason, forms are excellent. I like to use the wizard for this one.

  1. Field Selection: Pretty much the same as the Query Wizard. Select the table and the fields you want data to go to when it is entered.

  2. Set Up A Subform: Useful if you want a “form within a form” arrangement. If you choose to set up a subform, it will activate options for manual entry of the table and fields you want to use.

  3. Arrange Controls: Gives you options for the appearance and arrangement of the fields used for entry of data.

  4. Choose “Permissions” For Your Form: Consider this carefully, because if you're using this for gathering data from the general public (or have a co-worker who hates you enough to sabotage your work), you don't want them deleting or modifying your data. Under most circumstances, you should choose the first option, which only allows entry of new data and doesn't display existing data.

  5. Apply Styles: “Decorate” your form using the available styles.

  6. Set Name: Pretty straightforward. Decide what you want your form to be called. Watch for misspellings. Hit “Finish” when you're satisfied with your options.

What A Typical Form Might Look Like

Libreoffice Base Form

Reports

Base report

What in the world do you want all that data for? Reports are an easy way to put your data in a form that can be read at a glance. In this case, the wizard is the only way to create a report.

  1. Field Selections: Choose the table and fields you want to include in the report.

  2. Label Fields: If the existing field names are professional-looking enough, I usually just accept the defaults. Otherwise, they can be customized to your preferences.

  3. Grouping: Organize your data by field name.

  4. Sort: Choose the field to be sorted by ascending or descending order.

  5. Layout: Choose a layout that's appropriate for the topic of your report.

  6. Create Report: Decide whether you want a “Static” report that never changes or a “Dynamic” report that can be adjusted to your preferences.

A Report, With All The Defaults

Report with defaults

So that's what LibreOffice Base can do, in a nutshell. Create tables, queries, forms and reports to handle your data entry needs in a manageable way.

Go Advanced!

You are all over this and are ready to go advanced with your databases. Combine your LibreOffice smarts and Basic skills to get maximum functionality out of your databases.

Advertisement
Advertisement

Comments

Add a new comment - No HTML
You must be logged in and verified to post a comment. Please log in or sign up to comment.

Explore InfoBarrel

Auto Business & Money Entertainment Environment Health History Home & Garden InfoBarrel University Lifestyle Sports Technology Travel & Places
© Copyright 2008 - 2016 by Hinzie Media Inc. Terms of Service Privacy Policy XML Sitemap

Follow IB Technology