Query Summarizing Over a Calculated Field

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 Summarizing Over a Calculated Field

As a final example of a query, we will calculate the total sales of each person for each month and year. We will need to use the Date column from the Sales table twice, one to calculate the year and again to calculate the month, and we will also use the fields FirstName, LastName from the People table and Amount from the Sales table.

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 .

Step 3: Add fields to the query

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

Step 4: Set the calculation of the year and the month

Change the Field row of the first Date column to read YEAR("Date") and change the Field row of the second Date column to read MONTH("Date"). Set the Alias row to read Sale_Year and Sale_Month for these two columns. The YEAR and MONTH functions are among the many functions provided by the HSQL database engine. A complete list is available in the documentation at https://www.hsqldb.org/doc/1.8/guide/guide.html#N1251E.

Step 5: Set the grouping and the sum function

Set the Function row of every field except Amount to read Group. Set the Function row of the Amount column to read Sum and set its alias to Sales_Sum.

Step 6: Set the sorting

For a more logical presentation of the data, set the Sort column of the year, month and LastName columns to read ascending.

Be sure to save the query and the main Base document. We saved the query with the name GrpCalcColumn, and we will use it in the reports in the next section.

The final configuration of the query definition table and the query results are shown in Figure 198.

Figure 198: Definition and result of a query that groups on a calculated column</center