Make a Query With 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 >

Make a Query With a Calculated Field

Imagine you would like to calculate a commission for each sale that is 7% of the sale amount. The process is just like the last query except that at step 3 an additional column is added manually to the query description, as shown in Figure 195. Notice the fourth column where the Field is defined as "Amount" * 0.07. The name of the column upon which the calculation is based should be enclosed in double quotes. Besides the usual arithmetical operators of +, -, *, and /, many other functions such as square root (sqrt) and the trigonometric functions are available. The calculations may also use more than one column, so you could, for example, calculate the difference or ratio of two columns.

Some other details to note in the calculated column are that it has the text Commission written in its Alias row, and that the Table row is blank. These changes should make sense. The column is not stored in a table, and therefore it does not have a good default name. If the alias is not supplied, the text of the calculation is used, which is not often a helpful column header. Note also that the criterion in the Amount column has been removed. This was not required; it is perfectly possible to do the calculation only on rows that meet any number of criteria.

An important point in database design is that calculations based on field values should be done in queries and not stored in the regular database tables. The main reason for this is that if the underlying values are updated, the calculated values in a regular table will not be automatically updated. You have to explicitly recalculate them. In a query, on the other hand, only the code to run the query is stored, so any calculated values are updated every time the query is run.

Figure 195: A query with a calculated field