Using the Design View to Create a Query

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 >

Using the Design View to Create a Query

Make a Basic Query

Creating a query using Design View is not difficult but does require learning the conventions of the tool. We will start with a duplicate of the query from the last section and then make some more complicated queries.

Our first query will display all the sales with an amount greater than or equal to 100, sorted by date.

Step 1: Open the query in Design View

Click Create Query in Design View in the Query's pane of the main Base window.

Step 2: Add tables

Figure 191: Add Tables or Query window

1. Click Sales to highlight it.
2. Click  Add . Click  Close .

Move the cursor over the bottom edge of the Sales table (Figure 192) and drag it to make it longer and easier to see all the fields in the table.

Figure 192: Sales table in query

Step 3: Add fields to the query definition table at the bottom

Double-click the PersonID, Date, and Amount fields to add them to the query definition table.

The table at the bottom of the query window should now have three columns populated. The row labeled Visible is used to toggle the visibility of the field in the final query result. The default is to show all the fields.

Figure 193: Query definition table

Step 4: Set the criterion for the query

We want the query results to have an Amount greater than or equal to 100 and the dates to be sorted in ascending order.

1. Type >= 100 in the Criterion cell under Amount in the query definition table.
2. Choose ascending from the dropdown list in the Sort row of the Date column.
3. Click the Run Query icon in the Query Design toolbar, which is highlighted in red in Figure 194.

Figure 194: Query Design toolbar

Step 5: Save and close the query

Save the query and also save the main Base document.