Combinatorial querying with OpenOffice™ Base

A contribution by Axel Drefahl, axeleratio.com, September 23, 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  
5. Creating a query in OpenOffice™ Base

Now let's check out how we can customize ooBase queries. By combining query criteria you can filter database records in many ways to fit your specific interest. Continuing with the just designed query, we explore how to find minerals that contain a desired combination of rare-earth minerals (REEs):

1. In the Queries pane right-click QueryByREE and then select Edit from the context menu to open the Query Design window showing your current design from the last lesson.
2. Click into the empty cell next to the hasY cell in the Field row. From the selection list select CesbronList.hasLa.
3. Repeat step 2 for all other fields that you want to include in the query: CesbronList.hasCe to CesbronList.hasLu.
4. All cells of the Visible row show a check mark for field visibility in the Table Data View, which you may want to switch into when done with the query design. If you only expect to see the ID, mineral name and mineral formula, set the visibility to “No” by “clicking the check mark off.” 
5. At this point, the only set Criterion you see is TRUE for hasY. Delete the TRUE value. To see the minerals that contain cerium (Ce) and neodymium (Nd), enter TRUE into the respective Criterion cells. 
6. Click the Run Query Icon: the resulting table shows ten records with the minerals satisfying the query; including minerals that contain other REEs besides Ce and Nd (first picture below).
7. To see only those minerals that contain Ce and Nd, but no other REEs, enter FALSE into all other REE Criterion cells.
8. Again, click the Run Query Icon to see the four minerals satisfying the modified query (second picture below). 

Query result with records of minerals containing Ce, Nd and other REEs

Query result with records of minerals containing Ce and Nd and no other REEs

This example illustrated how to filter minerals of a desired REE combination. By leaving individual hasREE criterion cells empty or entering TRUE or FALSE, you can combinatorial filter minerals whether they certainly contain, certainly do not contain or may or may not contain a particular REE.

The ooBase file we have built up to now over six lessons is available as RareEarthMinerals_query.odb for testing and experimenting.