Learning in a digital world

OpenOffice.org Base is the database module initially released with OpenOffice.org version 2.0. Currently it is based on the HSQLDB database engine written in Java. Note that another database engine, SQLite, had been considered[citation needed] but the OpenOffice.org team decided to go with HSQLDB after evaluating the features, connectivity and embed quality of both database engines.

…

OpenOffice.org users, however, can choose to connect to external full-featured SQL database such as MySQL, PostgreSQL and even Oracle through ODBC or JDBC drivers. OpenOffice.org Base can hence act as a GUI frontend for SQL views, table design and query. In addition, OpenOffice.org has its own Form wizard to create dialog windows for form filling and updates. – Wikipedia

Comparison: See Tables, Queries, Reports, Error messages for how things are done in MS Access 2003.

import a csv file
Open a new database (File-New-Database). In your database click File – Open. Set “Files of type” to “All files”. Select the csv file and click Open. In the window that opens click OK. The csv file will open in Calc. In Calc click Ctrl+A to select the entire sheet. Click Ctr+C to copy it. Use the Window menu to switch to the database. In the Tables area right-click and choose Paste. Type the name of the table and check Create Primary Key. Click Next. Select all existing columns and click Next. In the Type Formatting window decide on the data types based on the data in the csv file. Use Text[VARCHAR], Integer[INTEGER] (numbers with no decimals), Number[NUMERIC] (currency and other numbers with decimals), Yes/No[BOOLEAN], and Date[DATE]. Click Create.

Note: If a field is TRUE/FALSE in the csv file then use Yes/No[BOOLEAN], but if it is Y/N use text[VARCHAR]. If not the data will not be imported. If it is Y/N you may convert it to TRUE/FALSE (1/0) with a formula like =IF(F2=”Y”;1;0).

view the structure of a table
Right-click the table’s name in the Tables section and choose Edit.

view a table’s data
Double-click the table’s name in the Tables section.

switching between structure and data view of a table
One may have the structure view and the data view open at the same time and switch between them using Alt+Tab. There is no other way to switch between the structure and data view using just one click.

autonumber
There is no datatype called AutoNumber. To get the same effect set a field to Integer and in Field Properties set AutoValue to Yes.

Field brackets in a query
Use double quotes. Example: “first_name”

Text in a query condition (criteria)
Use single quotes. Example: ‘OC’

Wild cards in a query
Like ‘A*’ finds all words that start with ‘A’.
Like  ‘A?s*’ finds all words that has ‘A’ and ‘s’ as the first and third letter respectively.
LIKE ‘*a?s*’ finds all words where ‘a’ and ‘s’ occur somewhere separated by any one letter.

Calculated field in a query
Type the expression in the Field row and the name in the Alias row.
Example:
Field: “price” * “units” * 0.90
Alias: Total reduced by 10%

To run a query
Press F5 or click the Run Query icon.

Grouping in a query
In the Function row select Group. If the function row does not show, click View – Functions or the Functions icon.

Sum, count, average, etc in a query
In the Function row select Sum, count, average, ….

If the function row does not show, click View – Functions or the Functions icon.

Number of decimal places in a query
After you run the query with F5, right-click the column heading in the result and choose Column Format. Click the Format tab and set the number of decimal places.

Sorting in a report
If you need to sort on a field that is not to be included in the report try this: sort on it in the query, but do not include it as a field in the report.

Report extension
An extension is available to assist in report creation. Sun Report  Builder creates stylish, complex database reports. You can define group and page headers, group and page footers, and calculation fields. It is available from http://extensions.services.openoffice.org/.

To install this extension, follow these steps:

1) Select Tools > Extension Manager from the menu bar. In the Extension Manager dialog, click Get more extensions here….
2) The OOo extensions page opens in your browser window. Find and select the extension you want to install and follow the prompts to install it. During installation, you will be asked to accept a license agreement.
3) When the installation is complete, the extension is listed in the Extension Manager dialog.

—

Update query
In ‘Create query in design view’ only select queries may created. For update, insert, append, and delete queries use the SQL option on the Tools menu. Do not use ‘Create query in SQL view’.
Example of an update query:  update “events” set “name” = ‘John’.

Count, sum, min, max, average in a report

• Count (see below for another way)
  Add a text box to the report footer. In the Data tab choose Counter for Data Field Type.
• Sum (see below for another way)
  Add a text box to the report footer. In the Data tab choose Function for Data Field Type. Choose the field in Data field. In Function choose Accumulation.
• Min/Max
  As for Sum, but in Function choose Minimum or Maximum.
• Average

1. In the Report Footer insert a text box and select it.

2. In the Data tab choose Counter for Data Field Type. Deselect the text box by clicking somewhere else in the Report Footer (if not, the function will disappear from the Report navigator).

3. Select the text box again and in the Data tab choose Function for Data Field Type. Choose the field in Data field. In Function choose Accumulation. Deselect the text box by clicking somewhere else in the Report Footer.

4. Select the text box again and set Date Field Type: Field or Formula and Data field: [AccumulationfieldnameReport]/[CounterReport].

Here is a longer way that is more complicated, but keeps the function names short:

1. In the Report Navigator define two functions:

Name: count
Formula: 1 + [count]
Initial value: 1
Deep traversing: No
Pre evaluation: Yes

Name: sum
Formula: [fieldname] + [sum]
Initial value: [fieldname]
Deep traversing: No
Pre evaluation: Yes

2. In the Report Footer insert a text box with:

Name: txtaverage
Date Field Type: Field or Formula
Data field: [sum]/[count]

Read more on this forum page.

—

Printing labels (example: Page 6, Paper 2, May 2008)

1. Open a database with existing tables and queries.
2. Select File > New > Labels.
3. On the Labels Tab: select Database and Table/Query. Type text in the label or select a Database field and click the left arrow to move it to the label. Select Sheet, Avery A4, and [User].
4. On the Format Tab: select width, height, margins, #columns, #rows, distance between columns (horisontal pitch – width), distance between rows (vertical pitch – height)
5. On the Options Tab: select Entire Page and Synchronize Contents.
6. Click New Document.
7. Click F4 to View > Data Sources.
8. Select all the rows and click the Data To Fields icon.
9. If you are not happy with the layout go back to step 2.

The above was researched by Akshayaa and Heta April 19, 2010. They came up with a method that makes it easier to format the labels, but is a bit longer:

1. Open a database with existing records and queries.
2. Select File > New > Labels OR Select the New Icon > Label.
3. In the window that opens, go to the Labels Tab and click on Sheet under ‘Format’.
4. Click on the Format tab and set the margins accordingly.
5. Specify the number of rows and columns, height and width of the label accordingly.
6. Go to the Options Tab and select Entire Page and also select Synchronize Contents.
7. Click New Document. A small window will appear saying ‘Synchronize Labels’ along with the document containing the labels.
8. Go to View > Data Sources OR click F4.
9. On the left hand side of the data source view, select your database and table/query.
10. If you have a title insert it into the top left label.
11. Click and drag the column headings needed onto the top left label.
12. Format them as required, using the formatting toolbar.
13. Click on ‘Synchronize Labels’ button.
14. Place the cursor at the end of the last field in the label and go to Insert > Fields > Other and then select Next Record > Insert > Close.
15. In the Data Source View, highlight the necessary records (rows) and on the Table Data Bar select Data To Fields icon.

—

Work in progress
This page is very much a work in progress. If you want to do something with Base, but don’t know how to, please ask or email me (jannordgreen@gmail.com). I will incorporate the answer in this page so you will be a fellow complice of making the page better.