Query with Two Related Tables

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 >

Query with Two Related Tables

An obvious deficiency in the previous queries is that the people associated with an expense or a sale are only identified by the value of the PeopleID field in the People table. Adding their names to the query output is simple because we have established relations between the People table and the Expenses and Sales tables. We will make a new query that displays sales information along with the name of the associated person.

Step 1: Start the query in Design View

From the main Base window, click Queries in the left pane and then Create Query in Design View from the Tasks pane.

Step 2: Add tables to the query

In the Add Query or Table dialog, click on People and then the Add button and do the same for the Sales table. Click Close. You will now have small representations of the two tables with a line running from the PeopleID field of one to the PersonID field of the other, as shown in Figure 196. You may want to resize the tables by dragging down their lower edges.

Figure 196: The People and Sales tables in a query

Step 3: Add fields to the query

Double-click on the FirstName and LastName from the People table and Date and Amount from the Sales table to add them to the query definition table.

We now have a query that will display the names along with the information from the Sales table. Save both the query and the main Base document. The query can be enhanced with criteria in any column or with calculated columns as shown in the previous sections.