Visual Query Design in LibreOffice Base

The Graphical Query Interface in LibreOffice Base provides a visual way to build SQL database queries without writing code manually. This guide covers how to launch the Query Design View, select data sources, establish table joins, apply filters and sorting criteria, and run your query to retrieve specific records efficiently.

1. Opening Query Design View

1. Open your database file (.odb) in LibreOffice Base.
2. In the left-hand Database pane, click on the Queries icon.
3. In the Tasks area, select Create Query in Design View….

The Query Design window will open alongside the Add Table or Query dialog box.

2. Adding Tables and Views

1. In the Add Table or Query dialog, select the tables or existing queries that contain the data you need.
2. Click Add for each required item.
3. Click Close once all necessary tables are displayed in the upper workspace.

3. Defining Table Relationships (Joins)

If you add multiple tables that share common keys:

1. Click and hold a field from one table (such as a Primary Key).
2. Drag and drop it onto the corresponding field (Foreign Key) in the other table. A line connecting the two fields will appear.
3. Double-click the join line if you need to modify the join type (e.g., Inner Join, Left Join, or Right Join).

4. Selecting Fields for the Output

The lower half of the interface contains the query design grid. To add fields to your query:

• Method 1: Double-click a field name in the table box in the upper area.
• Method 2: Click an empty cell in the Field row of the grid and choose a field from the dropdown list. The corresponding table will automatically populate in the Table row.
• Visible Checkbox: Check the Visible box to display the column in the results, or uncheck it if the field is only used for filtering.
• Alias: Enter a custom header name in the Alias row if you want to rename the column in the query results.

5. Applying Sorting and Criteria

Use the bottom grid to refine your results:

• Sort: Click the Sort row under any field to specify Ascending or Descending order.
• Criterion: Enter filter conditions in the Criterion row. For example:
  • Numbers: > 100, <= 50
  • Text: 'Active', LIKE 'Smith*'
  • Dates: #2023-01-01#
• Or: Use the Or rows beneath Criterion to specify alternative conditions.

6. Using Functions and Aggregations

To group data or calculate summary values:

1. Click the Functions button on the Query Design toolbar to display the Function row in the grid.
2. Select options like Count, Sum, Average, Min, Max, or Group for individual columns.

7. Running and Saving the Query

1. Click the Run Query button (or press F5) on the toolbar to test the query. The retrieved records will display in a preview pane above the design grid.
2. If adjustments are needed, modify the grid criteria and press F5 again.
3. Click the Save icon (or press Ctrl + S), enter a descriptive name for your query, and click OK.