Creating Auxiliary Tables for Data Entry

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 >

Creating Auxiliary Tables for Data Entry

It is often useful to have tables that provide a list of the possible values that a field in another table may have. For example, the Expenses table defined above has a field named ExpenseType. If people are allowed to enter anything in that field, a variety of words may be entered for one item. Buying gasoline might be entered as gasoline, gas, Gas, fuel, or several other possibilities. The entries might also have leading or trailing spaces that are hard to notice. All of that can be dealt with when the table is queried but limiting the entries, even if only you will use the database, leads to a cleaner data set.

For the present case of the ExpenseType field, we will make the simplest possible table with just one column that will serve as both the primary key and the useful data.

Click the Tables icon in the Database region of the main window and choose Create Table in Design View in the Tasks pane. Give the field the name ExpType and leave the field type as TEXT [VARCHAR]. Before leaving that row of the design table, right-click on the small green arrow at the left edge of the row and select Primary Key Save the table using File → Save with the name Expense_Categories and close it.

Adding Data to the List Table

You should now be back at the main Base window. In the Tables pane, right-click on the icon for Expense_Categories and select Open. Enter the values Supplies, Equipment, Travel, Meals as the first four values of the ExpType column. You can close the table without saving it, because the values were saved automatically as you entered them. Do be sure to complete the entry of the last value by pressing  ↵ Enter  or the down arrow to move to a new row before closing the window.

An alternative to this very simple table structure is to define a primary key column that is separate from the values you want to display in the Form when entering data. This is discussed further in the next section.