Difference between revisions of "LibreOffice-Suite-Base/C2/Create-queries-using-Design-View/English-timed"

||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

||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,

||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 also see a small popup window at the top, that says Add Table or Query.

||And we will need all the three tables for our query to generate the history of the 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.

||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.

||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.

||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.

||Also the corresponding table names in the third row.

||Next, let us look at the ‘Alias’ in the second row.

||So let us type in aliases as shown in the image.<pause>

||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’.

||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.

||Next, we will go to the ‘Function’ row.

This is used to create complex queries.

We will skip this for now.

||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’.

||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.

||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.

||For example, let us remove the Checked In criterion. <pause>

||Now let us run the query by pressing F5 again.

||Next, let us save this query, by pressing Control S. This opens a small popup window.

||and then click on 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

||Generate a list of the books issued to the member Nisha Sharma.

The list should be chronological by Issue date.

||This brings us to the end of this tutorial on Creating Queries in Design View in LibreOffice Base

||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.

||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

||This script has been contributed by Priya Suresh, DesiCrew Solutions, and this is Soundharya DesiCrew solutions, signing off. Thanks for joining.