Using the Wizard to Create a Table

Getting Started with OpenOffice 4

Chapter 7: Getting Started with Base

< Previous Section

Next Section >

• What is Base?
• Planning a database
• Creating a new database
• Creating database tables
  • Using the wizard to create a table
  • Creating a table by copying an existing table
  • Creating tables in design view
  • Creating auxiliary tables for data entry
• Defining relationships
• Creating a database form
  • Using the wizard to create a simple form
  • Using the wizard to make a form with a subform
  • Laying out a form with multiple controls
  • Creating forms and subforms in design view
• Accessing other data sources
• Using data sources in Apache OpenOffice
• Creating queries
  • Using the wizard to create a query
  • Using the design view to create a query
  • Make a query with a calculated field
  • Query with two related tables
  • Query that summarizes data
  • Query summarizing over a calculated field
• Creating reports
  • Simple static report
  • Grouped dynamic report

< Previous Page

Next Page >

Using the Wizard to Create a Table

Wizards are designed to do the basic work. Sometimes this is not sufficient for what we want; in those cases we can use a wizard as a starting point and then build upon what it produces.

The Table Wizard in Base contains two categories of suggested tables: business and personal. Each category contains sample tables from which to choose. Each table has a list of available fields. We can delete some of these fields and add other fields.

A field in a table is one piece of information. For example, a price list table might have one field for the item name, one for the description, and a third for the price.

Every table requires a Primary key field. (What this field does will be explained later.) We will use this field to number our entries and want that number to automatically increase as we add each entry.

Click Use Wizard to Create Table. This opens the Table Wizard (Figure 161).

Step 1: Select fields

We will use the Expenses sample table in the Business category to select the fields we need for our first table.

1. Category: Select Business. The Sample tables drop down list changes to a list of tables appropriate for a business.
2. Sample tables : Select Expenses. The Available fields box changes to a list of available fields for this table.
3. Selected fields : Using the  >  button, move the following fields from the Available fields window to the Selected fields window in this order: AmountSpent, DatePurchased, EmployeeID, ExpenseType. You can select multiple fields by pressing the  Ctrl  key while clicking and then move all the chosen fields at once.At this point, you could select another sample table and move fields from it to the Selected fields list, but that is not necessary in this case.
4. If you make a mistake in selecting fields, click on the field name in the Selected fields list and use the  <  button to move it from the Selected fields list back to the Available fields list.
5. If you make a mistake in the order of the selected fields, click on the field name that is in the wrong order and use the Up or Down arrow on the right side of the Selected fields list to move the field name to the correct position.
6. Click  Next .

Notice that none of the field names include spaces. It is possible to have spaces in field names, but it is best to avoid them.

Figure 161: Selecting fields for the table

Step 2: Set field types and formats

In this step you give the fields their properties. When you click a field, the information on the right changes. (See Figure 162) You can then make changes to meet your needs. Click each field, one at a time, and make the changes listed below. Some questions you might consider in a real application are included in the changes.

If any of these fields requires an entry, set Entry required to Yes. An entry with that field blank will then not be allowed. In general, only set Entry required to Yes if something must always be put in that field. By default, Entry required is set to No.

Figure 162: Changing field types

• AmountSpent: Change Decimal places to 2. You should consider whether you want to allow an expense with no amount recorded. If not, change Entry required to Yes.
• DatePurchased: Notice the Field type is Date, which is correct. If you want to require a date, change Entry required to Yes.
• EmployeeID: If you want to require that every expense be assigned to an employee, change Entry required to Yes.
• ExpenseType: The Field type is Text [VARCHAR]. With Length set to 50, this field must be no more than 50 characters long. Is that enough?

In Base the maximum length of each field must be specified on creation. It is not easy to change this later, so if in doubt specify a greater length. Base uses VARCHAR as the default field format for text fields. This format only uses the actual number of characters in a field up to the limit set. So, a field containing 20 characters will only use space for 20 characters even if the limit is set at 50.

• When you have finished, click  Next .

Each field has a Field Type, which must be specified. Types include text, integer, date, and decimal. If the field is going to have general information in it (for example, a name or a description), use text. If the field will always contain a number (for example, a price), the type should be decimal or another numerical field. The wizard picks the right field type, so to get an idea of how this works, see what the wizard has chosen for different fields.

Step 3: Set primary key

1. Create a primary key should be checked.
2. Select the option Automatically add a primary key and check Auto value.
3. Click  Next .

A primary key uniquely identifies an item (or record) in the table. For example, you might know two people called “Randy Herring” or three people living at the same address and the database needs to distinguish between them. The simplest method is to assign a unique number to each one: number the first person 1, the second 2, and so on. Each entry has one number and every number is different, so it is easy to say “record ID 172”. This is the option chosen here.

Step 4: Create the table

1. If desired, rename the table at this point. If you rename it, make the name meaningful to you. For this example, make no changes.
2. Leave the option Insert data immediately checked. We will view the table but not actually enter data.
3. Click  Finish  to complete the table wizard.

A window will appear titled Expenses – Finance – OpenOffice Base: Table Data View. This displays the empty table with the fields (columns) you selected plus a column named ID that is labeled <AutoField> and it was created in Step 3 above. The ID column will be filled with integers automatically when data is added and those numbers will never repeat even if data is deleted. The ID column is the Primary Key of the table.

Close the window displaying the table, and you will be back to the main window of the database with the listing of the tables, queries, forms, and reports. Notice that a table named Expenses is now listed in the Tables portion of the window. Now is a good time to save the Base file.