Thursday, September 22, 2016

Creating a query in OpenOffice™ Base

A contribution by Axel Drefahl, axeleratio.com, September 22, 2016.

This post continues my preceding OpenOffice™ Base (ooBase) lessons:
  1. Exploring OpenOffice Base: an example involving rare-earth mineral data  
  2. Structuring rare-earth mineral data in an OpenOffice Base table
  3. Modifying the column layout of an existing OpenOffice Base table  
  4. Defining and creating relationships between OpenOffice Base tables 
A query can answer specific questions about data stored in an ooBase application. Queries support you in maintaining and updating the database. You will consider using a query to review, filter and modify data as well as performing tasks such as calculations and report generation.

Running a query results in a table. Before running it, we need to create it. In the following example we use the ooBase Design View tool to create a query that selects all minerals from CesbronList that contain yttrium (Y):

  1. Open RareEarthMinerals_2tables.odb. If not yet done, create the relationship as shown in the previous lesson.
  2. Click Queries in the Database pane.
  3. In the Tasks pane, select Create Query in Design View... to open the Query Design window with the pop-up Add Table or Query dialog.
  4. Add CesbronList and Close the Add Table or Query dialog.
  5. From the appearing CesbronList field list, drag the names of those fields you want to include in your query into the cells of the Field row of the table in the lower section of the Query Design window: drag ID, MinName, MinFormula and hasY into the first, second, third and fourth Field cell, respectively. Notice that the first four Table cells indicate the source table, CesbronList, and the first four Visible cells include the Yes check mark.  
  6. To select all minerals with hasY set to Yes, click into the hasY cell of the Criterion row and enter TRUE.
  7. Click the Run Query Icon (or press F5 key). A table (view) with 74 records representing exactly those minerals that contain yttrium appears between the menu bar and the query design table.
  8. Open the Save As dialog by selecting File > Save from the menu in the Query Design window and enter QueryByREE as Query name, then press the OK button. Close the Query Design window.   
  9. In the Queries pane, double-click QueryByREE to open and review the Table Data View window with the mineral data for the Y-containing minerals.

OpenOffice Base Query Design window
Query Design window after running the query by pressing the Run Query Icon (fourth icon from right in the upper menu bar in the above cut-out section

You can come back to your query at any time to reset the query critera and to include not-yet-selected fields into your query. In the next lesson we are going to extend QueryByRee such that we are enabled to filter minerals by any desired combination of rare-earth elements. 

No comments:

Post a Comment