LibreOffice-Suite-Base/C2/Create-queries-using-Design-View/English-timed

From Script | Spoken-Tutorial
Revision as of 15:00, 9 July 2014 by Gaurav (Talk | contribs)

Jump to: navigation, search
Time Narration
00:00 Welcome to the Spoken tutorial on LibreOffice Base.
00:04 In this tutorial, we will learn how to
00:06 Create a query by using the Design View.
00:10 Add tables to the Query Design window
00:13 Select fields.

Set up aliases, Set up sorting order And provide search criteria for a query

00:23 For this, let us consider our familiar Library database example.
00:29 In this Library database, we have stored information about books and members.
00:37 And, we also have a table to track the books issued to the members.
00:45 Now we will create a new query, to list all the books that have been issued to the members,
00:54 In other words, let us generate a history of books that have been issued to the members.
01:03 Lets open the Library database.
01:07 Let us click on the Queries icon on the left panel.
01:13 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,
01:28 And we also see a small popup window at the top, that says Add Table or Query.
01:39 Here is where we will define the source of the data for the query.
01:46 And we will need all the three tables for our query to generate the history of the books issued to members.
01:57 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.
02:11 We will similarly add the BooksIssued table and the Members table <pause>
02:19 We now see that, the three tables have appeared in the background query design window.
02:26 Let us now ‘Close the popup window.
02:31 This brings the Query design window to the foreground.
02:39 Notice that the three tables are in the top half of the window
02:46 Here let us introduce some space among these tables.
02:53 Let us click, drag and drop the Members table to the far right.
03:01 And then, click, drag and drop the BooksIssued table to the centre.
03:11 Now we see lines linking these tables and these are the relationships that we had established earlier.
03:23 We can double-click on the lines to see the relationship details.
03:30 For now, let us see the bottom half of the Query design window.
03:37 This area has several rows of cells. As we design the query, we will fill these up.
03:48 First, we will check the Field column.
03:53 This is for specifying the fields we need to display in the result set.
04:01 To do this, we will first double click on the Title field in the Books table in the upper half of the window.
04:12 Next is the Name field in the Members table.
04:17 And then the Issue Date field in the BooksIssued table.
04:24 Next, the Return date,the actual return date

And finally the checked in field.

04:34 Notice these fields in the bottom half of the window in the first row.
04:44 Also the corresponding table names in the third row.
04:50 Next, let us look at the ‘Alias’ in the second row.
04:57 This is where we can enter descriptive names for the selected fields.
05:04 So let us type in aliases as shown in the image.<pause>
05:11 And we are done with the aliases.
05:15 Next, let us look at the Sort row.
05:21 We can specify the ordering of the result set here
05:26 Since we need a history of the books issued, we will order it chronologically.
05:34 Meaning we will sort the result set by the Issue Date in ascending order.
05:43 For this, we will click on the empty cell in the Sort row, under the Issuedate field.

And let us click on ‘Ascending’.

05:56 Okay, we will go to the next row - ‘Visible’
06:02 Here we can set the visibility of the fields we selected by checking or un-checking them.
06:11 Notice that, by default, all of them are checked.
06:17 Next, we will go to the ‘Function’ row.

This is used to create complex queries. We will skip this for now.

06:27 and we will go to the ‘Criterion’ row.
06:32 This is where we can limit the result set to a simple or complex set of criteria.
06:40 For example, we can query for those books only, which were issued but have not been returned by members,
06:49 meaning, only those that are not checked in.
06:54 So let us click on the empty cell in this row, under the CheckedIn field

and type in ‘Equals Zero’.

07:06 Thats it, let us run this query now.
07:10 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.

07:27 Can you see some data in the upper half of the window?
07:32 These are the results of your query.
07:36 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.

07:51 Now we can go to the query design area below and change it any way we want .
08:00 For example, let us remove the Checked In criterion. <pause>
08:07 Now let us run the query by pressing F5 again.
08:15 This time we see a longer list of data returned from the query.
08:23 Next, let us save this query, by pressing Control S. This opens a small popup window.
08:34 Let us give a descriptive name to our query here.
08:38 Let us type ‘History of Books Issued to Members’
08:46 and then click on Ok button. and close this window.
08:52 We can open this saved query by double clicking on the query name in the main Base window.
09:01 So there, we have successfully created a query using the Design View
09:09 Here is an assignment:
09:12 Generate a list of the books issued to the member Nisha Sharma.

The list should be chronological by Issue date.

09:24 This brings us to the end of this tutorial on Creating Queries in Design View in LibreOffice Base
09:31 To summarize, we learned how to:
09:33 Create a query by using the Design View.

Add tables to the Query Design window Select fields

09:41 Set up aliases,

Set up sorting order And provide search criteria for a query.

09:49 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
10:10 This script has been contributed by Priya Suresh, DesiCrew Solutions, and this is Soundharya DesiCrew solutions, signing off. Thanks for joining.


Contributors and Content Editors

Gaurav, Minal, PoojaMoolya, Sandhya.np14