LibreOffice-Suite-Base/C2/Create-queries-using-Design-View/English
| Visual Cue | Narration |
|---|---|
| Show Slide Number 1
Spoken tutorial on LibreOffice Base: Create Queries in Design View Learning Objectives
|
Welcome to the Spoken tutorial on LibreOffice Base.
In this tutorial, we will learn how to Create a query by using the Design View. Add tables to the Query Design window Select fields. Set up aliases, Set up sorting order And provide search criteria for a query |
| Show Slide Number 2
Creating a query by using the Design View Example: Generate a history of books that have been issued to the members. |
For this, let us consider our familiar Library database example.
In this Library database, we have stored information about books and members. And, we also have a table to track the books issued to the members. Now we will create a new query, to list all the books that have been issued to the members, In other words, let us generate a history of books that have been issued to the members. |
| In the Base main window,
Click on 'Queries' on the left panel, click on 'Create Query in Design view' Show appropriate cursor movement Point mouse over table names as narration goes forward Click on the Books table in the list and then click on the Add button on the right in the popup window. Click on the BooksIssued table in the list and then click on the Add button. Click on the Members table in the list and then click on the Add button. Point mouse over the the three added tables in the background window Click close button in the popup. |
Lets open the Library database.
Let us click on the Queries icon on the left panel. On the right panel, we will click on the ‘Create Query in Design view’. We now see a new window which is also called the Query Design window, And we can also see a small popup window at the top, that says Add Table or Query. Here is where we will define the source of the data for the query. And we will need all the three tables for our query to generate the history of books issued to members. We will do this by clicking on the Books table in the list and then clicking on the Add button on the right in the popup window. We will similarly add the BooksIssued table and the Members table <pause> We now see that, the three tables have appeared in the background query design window. Let us now ‘Close the popup window. This brings the Query design window to the foreground. |
| Click, drag and drop the Members table to the far right. Then click, drag and drop the BooksIssued table to the centre.
Double click on the line between Books and BooksIssued table. Close the new popup window. |
Notice that the three tables are in the top half of the window.
Here let us introduce some space among these tables. Let us click, drag and drop the Members table to the far right. And then, click, drag and drop the BooksIssued table to the centre. Now we see lines linking these tables and these are the relationships that we had established earlier. We can double-click on the lines to see the relationship details. |
| Point mouse over lower half of window.
Now, point mouse over the field names are they are narrated. Double click on the Title field in the Books table on the upper half of the window Double click on the following fields one by one: Name field in members table IssueDate in BooksIssued ReturnDate ActualReturnDate and CheckedIn fields Point mouse over the first row Point mouse over the third row |
For now, let us see the bottom half of the Query design window.
This area has several rows of cells. As we design the query, we will fill these up. First, we will check the Field column. This is for specifying the fields we need to display in the result set. To do this, we will first double click on the Title field in the Books table in the upper half of the window. Next is the Name field in the Members table. And then the Issue Date field in the BooksIssued table. Next, the Return date, the actual return date And finally the checked in field. Notice these fields in the bottom half of the window in the first row. And also the corresponding table names in the third row. |
| Point mouse at the Alias row.
Type in the following in the Alias row: Book Title under the title field Member Name under the name field Book Issue Date under the IssueDate field Return Date under the ReturnDate field Actual Return Date under the ActualReturnDate And, Checked In Status under the CheckedIn field. |
Next, let us look at the ‘Alias’ in the second row.
This is where we can enter descriptive names for the selected fields. So let us type in aliases as shown in the image.<pause> And we are done with the aliases. |
| Point mouse over the Sort in the fourth row
Click on the empty cell in the Sort row, under the Issuedate field. In the dropdown box, click on ‘Ascending’. |
Next, let us look at the Sort row.
We can specify the ordering of the result set here. Since we need a history of the books issued, we will order it chronologically. Meaning we will sort the result set by the Issue Date in ascending order. For this, we will click on the empty cell in the Sort row, under the Issuedate field. And let us click on ‘Ascending’. |
| Point mouse over the ‘Visible’ in the fifth row | Okay, we will go to the next row - ‘Visible’.
Here we can set the visibility of the fields we selected by checking or un-checking them. Notice that, by default, all of them are checked. |
| Point mouse over the ‘Function’ in the sixth row. | Next, we will go to the ‘Function’ row.
This is used to create complex queries. We will skip this for now. |
| Point mouse over the ‘Criterion’ in the seventh row
Click on empty cell in this row, under the Checked In field. Type ‘=0’ |
and we will go to the ‘Criterion’ row.
This is where we can limit the result set to a simple or complex set of criteria. For example, we can query for those books only, which were issued but have not been returned by members, meaning, only those that are not checked in. So let us click on the empty cell in this row, under the CheckedIn field and type in ‘Equals Zero’. |
| Go to Edit menu,
click on Run Query |
Thats it, let us run this query now.
We can use the keyboard shortcut F5, or click on the Edit menu at the top of the window, and then click on ‘Run Query’ at the bottom. |
| Point mouse over the result set
As narration goes forward In the query design area below, delete the entry ‘=0’ in the criterion row. Go to Edit menu, click on Run Query. Point mouse over result set. |
Can you see some data in the upper part of the window?
These are the results of our query. Notice that, we see a history of books issued to members and ordered by Issue Date. Also notice that none of the books are checked in. Now we can go to the query design area below and change it any way we want . For example, let us remove the Checked In criterion. <pause> Now let us run the query by pressing F5 again. This time we see a longer list of data returned from the query. |
| Press Control S.
Type in ‘History of Books Issued to Members’. Click on Ok button. Close the window. Go back to main window. Click on the query name ‘History of Books Issued to Members’ |
Next, let us save this query, by pressing Control S. This opens a small popup window.
Let us give a descriptive name to our query here. Let us type ‘History of Books Issued to Members’ and click on the Ok button. and close this window. We can open this saved query by double clicking on the query name in the main Base window. So there, we have successfully created a query using the Design View. |
| Show Slide Number 3
Assignment: Generate a list of the books issued to the member Nisha Sharma. The list should be chronological by Issue date. |
Here is an assignment:
Generate a list of the books issued to the member Nisha Sharma. The list should be chronological by Issue date. |
| Show Slide Number 4
Summary
|
This brings us to the end of this tutorial on Creating Queries in Design View in LibreOffice Base
To summarize, we learned how to: Create a query by using the Design View. Add tables to the Query Design window Select fields Set up aliases, Set up sorting order And provide search criteria for a query. |
| Acknowledgement Slide | Spoken Tutorial Project is a part of the Talk to a Teacher project, supported by the National Mission on Education through ICT, MHRD, Government of India. This project is co-ordinated by http://spoken-tutorial.org. More information on the same is available at the following link http://spoken-tutorial.org/NMEICT-Intro. This script has been contributed by Priya Suresh, DesiCrew Solutions Pvt Ltd, signing off. Thanks for joining. |
