76. Create a Calc Database
BEFORE YOU BEGIN
75 About Calc Databases
77 Import Data into a Calc Database
You already know how to create a Calc database if you know how to create a Calc spreadsheet. A database consists of records and fields (see 75 About Calc Databases ), and a sheet inside a spreadsheet consists of rows and columns that perform the same purpose as records and fields when you type data into them.
76. Create a Calc Database
Each field in your database must have a name . The simplest way to designate a field name is to type a label atop each column in your database. That label, such as Address or DateHired , becomes the name of that field. A field contains as many values as you have records in the database. For example, if your database has 100 rows, your database has 100 records, and each field in the record contains 100 values (some may be blank).
A field can consist of a calculated value that contains a formula based on other data in the record.
| | Type the Field Names
To create field names for your database, you only need to type a one-line label atop each column in your spreadsheet's database. First, label your spreadsheet with an appropriate title that describes the database you're creating. Use the standard character-formatting tools (see 12 Apply Character Formatting
) to make the title and field names stand out.
61 Center a Heading over Multiple Columns shows you how to center your database title in a wide column across the top of the fields.
| | Enter the Data
Type the data that falls beneath the field names. Reserve one row for each record in your database. Use the standard data-entry techniques you'd use for any spreadsheet (such as pressing Tab
at the end of a value, and so on).
You may enter formulas if you wish. In a database that represents a checkbook register, for example, the Balance
field (column) should be calculated and consist of a running total from your very first deposit that you used to set up the account in the first record.
You can sort database records into many different orders. For example, you can sort a checkbook record into alphabetical order based on whom you wrote checks to. This, however, makes the calculated Balance field nonfunctional for that view of your data. Only when sorted by date would a calculated balance field be useful.
If you want to use a database you create in Calc in the Base component of OpenOffice.org, you must first register the Calc database with OpenOffice.org. See 140 Access an Existing Database for more information.
| | Request a Data Range
Once you've entered some or all of your database records (you can always add more later), you must define a data range
for the data so that Calc will know which rows and columns hold database data. Select your database rows and columns that will make up your database. If you include the column titles above each column, Calc makes those titles the field names that you can refer to later when accessing the database.
KEY TERM Data range
A range you define that specifies exactly which rows and columns comprise the records and fields of your database. If you include column title cells , Calc makes those titles the field headings in your database.
Select Data, Define Range
from the menu to display the Define Database Range
Define the Data Range
Type a name for the data range in the Name text box. Choose a simple name that you associate with this collection of data (don't include any spaces). Click the Define Database Range dialog box's More button to display extra options you'll often need to define the data range. If you've included column titles and you want Calc to make them field names, make sure to select the Contains column labels option. Calc fills in your selected range for you (using absolute addressing with the sheet name preceding the range), but you can change the range if you wish to. The Shrink button collapses the Define Database Range dialog box in case you want to look at your sheet once again without closing the dialog box (click Shrink again to restore the dialog box to its original size ).
Click Add to add the data range to Calc's stored databases. You can define multiple data ranges per sheet, but generally you'll be able to track your data better if you keep each sheet a separate database range. If you ever want to remove a defined data range, display the Define Database Range dialog box once again but click the Delete button instead of the Add or OK button to remove the data range from Calc's database collection.
If you plan to add data later to the database, click to check the Insert or delete cells option so that Calc automatically updates the data range when you append data to the end of it or remove rows from within the range. The Keep formatting option maintains the cell formats with the data.