Do you need to make a database, but fear it’s too much of a pain or you don’t have the right tools? Don’t worry: it’s easy, free, and useful, too. Use the free OpenOffice.org office suite to get your data in shape for mail merges, queries, or useful analysis of your business data.

What’s the point of making a database?

Not everyone needs a database. But sooner or later, anyone who has to keep track of and retrieve important information is going to benefit from one. Databases are for storing data, of course, but their benefit comes in letting you get at the data you want, and in displaying or printing it the way you want. For instance, once you have a database, you can, of course, do mail merges—whether it’s a letter to each of the 500 people you met at a free software convention or printing labels for each of the 870 legal DVD backups you own. Databases are also necessary for setting up data entry or data viewing forms; creating relations between tables so you can print connected data; or for creating a list that shows the average number of items your customers bought per purchase during the holiday shopping season.

Not everyone needs a database. But sooner or later, anyone who has to keep track of and retrieve important information is going to benefit from one

What kind of database do you create?

Once you’ve decided to create a database, the way ahead looks simple. You just choose:

File→New→Database

However, then one last question remains: what type of database do you create? Do you connect straight to your Oracle database? (This is best only if you are or have access to an Oracle guru.) Do you investigate some of the simpler choices such as spreadsheets, or if you are well informed or psychic, do you choose the entirely invisible option of importing existing data?

If you’re a database god and enjoy spending time deep in a database—well, you can do anything you want. If you want the most results for the least trouble, here’s what I recommend.

• Get your data in a spreadsheet, then create a database file that can read the spreadsheet. This is simple and if you don’t need advanced database features, it’s all you need. See Creating a simple database that connects to a spreadsheet.
• Or get your data in a spreadsheet, and paste it into Base to create a native Base database. This involves slightly more work but gives you a great deal more power. See Creating a native OpenOffice.org Base database.

Creating a simple database that connects to a spreadsheet

The simplest approach is to simply create an OpenOffice.org database file that points to a spreadsheet chock full of data. The spreadsheet doesn’t have the power to do mail merges and the other database-related features I mentioned earlier, but the database file pointing to it can. It acts as a middle man saying “The data’s over there, with these fields—go get it”.

The simplest approach is to simply create an OpenOffice.org database file that points to a spreadsheet chock full of data

You either have your data in a spreadsheet already, or you can get it into a spreadsheet pretty easily. You’ll want to have it set up to look something like this, with field labels across the top, and with each piece of data separate. See figure 1.

Figure 1: Spreadsheet data

Now you’re ready to create the database. It’s pretty simple.

1. Choose File→New→Database.

2. In the first window, select Existing Data Source, and Spreadsheet type. Click Next. See figure 2.

Figure 2: Choosing to create a spreadsheet-based database

3. In the next window, point to the full path (I’ve just shown the filename here) of the spreadsheet you want to use. Click Next. See figure 3.

Figure 3: Specifying the spreadsheet name and path

4. Leave all the checkboxes marked. You don’t need to edit the database but the first time, at least, it’s good to take a look at what the main database window looks like. Click Finish. See figure 4.

Figure 4: Finishing the database

5. Name the database. This name will show up when you do mail merges, and it’s the name and location you’ll look for when you want to do things with the database (like create queries or reports). Click Save. See figure 5.

Figure 5: Naming and saving the database

6. You’ll see the database. Click the Tables icon at the left, and select a table name. Each sheet containing data in your spreadsheet will become a table. Now, on the right side, instead of None, select Document. You’ll see the data in the spreadsheet. See figure 6.

Figure 6: Viewing the database file that connects to your spreadsheet

There’s nothing else you have to do. Here’s a little more information, though.

Updating the data

When you have more data, just add it to the spreadsheet. When you want more tables, just add the data to another sheet in the spreadsheet.

When you have more data, just add it to the spreadsheet

What you can’t do with this type of database

There are some fairly cool features that you might or might not need that you won’t have available. You can’t, for instance, use the functions that can tell you: for every invoice, what was the average total amount on the invoice. You can’t create data entry forms. You can’t join two tables to relate the data. But you can create queries, and reports, and data view forms. (Click the Forms icon on the left side in your database editing window, and use the wizard to create the form.) See figure 7.