Queries in Base: Design View, Criteria and Sorting

Hello students, welcome to Kwickprep. A table has marks of five hundred students. Who scored above ninety in IT, and in what order? Today we will build queries in Base with the wizard and in Design View, add criteria, sort results and choose fields.

First, what is a query? A query is a question you ask about the data stored in tables. It shows only the fields and records you want, like students who scored above ninety. The original table is not changed when a query runs. And once saved, the query can be run again any time, even after new records are added.

We will use a table called result. Riya is from Surat and scored ninety two. Aman is from Rajkot and scored sixty eight. Neha is from Surat and scored seventy five. Kabir is from Vadodara and scored eighty eight. Each query we build will ask a question of these four records.

Click Queries on the left of the database window, and Base offers three ways. Use Wizard to Create Query walks a beginner through questions. Create Query in Design View gives full control using a grid. Create Query in SQL View lets you type SQL commands directly.

Let us first use the wizard. Choose Use Wizard to Create Query. In field selection, pick the result table and move Name and Marks to the right with the arrow button. In sorting order, choose Marks and select descending. In search conditions, choose Marks, is greater than, and type eighty. Skip the optional steps, and on the overview step, name the query Toppers. Click Finish, and the results appear.

The wizard has more steps that you can often skip. Detail or summary lets you show every record, or only totals like sum and average. Grouping shows results per group, such as per city, and works only with summary queries. Aliases give friendlier column headings, like Total Marks. Overview is the last step, where you name the query and click Finish.

The wizard is quick, but Design View gives more control. Choose Create Query in Design View. The Add Table or Query box opens, so select result, click Add, then Close. The top half now shows the table with its list of fields. The bottom half is the design grid, where each column describes one field of the query.

The design grid has several rows, and each one has a job. Field holds the field name, added by double-clicking it in the top half. Alias gives a new column heading. Table shows which table the field comes from. Sort sets ascending or descending order. Visible decides if the field is shown. Criterion holds the condition that records must meet.

To choose fields, double-click them in the field list, or drag them into the grid. Name is ticked in Visible, so it is shown. City is in the grid but not ticked, so it can hold a condition without being shown. Marks is ticked, so it is shown. Press F5 or click Run Query to see the results.

Criteria are conditions typed in the Criterion row. Greater than or equal to seventy five keeps marks of seventy five or more. Text values go inside single quotes, so quote Surat, unquote finds exactly Surat. Less than followed by greater than means not equal to. Between seventy and ninety includes both end values. Like with R and a percent sign finds names starting with R, because percent stands for any characters. The design grid in Base also accepts a star.

You can combine conditions in two ways. Conditions typed on the same Criterion row are joined with and. For example, city Surat and marks above eighty, and both must be true. A condition typed in the Or row below is joined with or. For example, marks above ninety or city Rajkot, and either one is enough.

Pause and predict. On the Criterion row, city is Surat. On the same row, marks are greater than eighty. Now you run the query. Which names appear? Only Riya, because Neha is from Surat but scored seventy five.

The Sort row arranges the results. Ascending means A to Z for text, and smallest first for numbers. Descending means Z to A, or largest first. If two fields are sorted, the leftmost field in the grid is sorted first. So city ascending, then marks descending, lists Rajkot first, then Surat with Riya before Neha, then Vadodara.

Finally, run and save your query. Press F5 or click Run Query to see the results above the grid. Press Ctrl+S and type a name, such as Surat Toppers. Later, double-click the saved query to run it again with the latest data. Base also lets you turn Design View off to see the same query written as SQL.

Let us revise what we learned today. A query shows only the fields and records you choose, without changing the table. The wizard asks for fields, sorting, conditions and a name. Design View uses a grid with Field, Sort, Visible and Criterion rows. Conditions on the same row mean and, the Or row means or, and F5 runs the query. Practise by finding toppers from your own class table.