↓
 

The Place For Free Online Training Courses

Improve Your Career Prospects

  • Home
  • Office Software
    • Understanding Spreadsheets
    • Advanced Spreadsheets
    • Understanding Word Processing
    • Advanced Word Processing
    • Understanding Presentations
    • Advanced Presentations
    • Understanding Databases
    • Advanced Database
    • Math
  • ICDL Syllabus
    • ICDL Base Module Syllabus Word Processing
    • ICDL Base Module Syllabus Spreadsheets
    • ICDL Base Module Syllabus Presentations
    • ICDL Base Module Syllabus Databases
    • ICDL Advanced Module Syllabus Word Processing
    • ICDL Advanced Module Syllabus Spreadsheets
    • ICDL Advanced Module Syllabus Presentations
    • ICDL Advanced Module Syllabus Databases
  • FaQ
1 2 >>

Advanced Database Lessons

Post navigation

← Previous Post

Advanced Database – Database Development and Use

The Place For Free Online Training Courses

Databases are systems used for the storage, maintenance and retrieval of data. In the modern ‘internet age’, databases are used widely to power ‘dynamic websites’, meaning prices, product details, mailing lists and other large groups of data can be easily … Take lesson

Advanced Database – The ‘life cycle’ of a database

The Place For Free Online Training Courses

Logical Design – must be carefully designed to incorporate all the data and applications needed, including possible future changes and developments. Define the output(s) required, then work backwards to include all the elements needed. Database Creation – set up tables, … Take lesson

Advanced Database – Structured Query Language (SQL)

The Place For Free Online Training Courses

Structured Query Language (SQL) is used to manage data within a database. It uses relational algebra to create commands that retrieve or maintain the data. Some examples of commands are CREATE – to create tables, RENAME – to rename tables, … Take lesson

Advanced Database – Tables

The Place For Free Online Training Courses

For this module we are going to create a database for a car repairs garage. We are going to set up some tables and a form in this section to demonstrate the principles. First, create a new database in LibreOffice … Take lesson

Advanced Database – Form

The Place For Free Online Training Courses

Create a form for inputting jobs. Go to forms in the main window, then click on ‘Use wizard to create form’. Select all of the fields from the ‘Jobs’ table, select the arrangement you prefer. Name the form ‘Jobs Entry’ … Take lesson

Advanced Database – Create, modify or delete a lookup in a field

The Place For Free Online Training Courses

Libreoffice Base doesn’t allow you to create a ‘lookup’ list in a table in the way it does in MS Access. We will have to use a form to do the same thing. Right click on the Jobs Entry form … Take lesson

Advanced Database – Create, modify, delete an input mask in a field

The Place For Free Online Training Courses

In Base you can only create input masks in a form (not a table as in MS Access). We are going to edit the date field to only allow dates to be inputted in the format DD** MMM YY (e.g. … Take lesson

Advanced Database – Set data entry for a field/column: required, not required

The Place For Free Online Training Courses

Open the ‘Jobs’ table in design mode. If we want to ensure that data is always entered for a certain field, click on the relevant field. In the Field Properties box at the bottom of the screen, select ‘Yes’ from … Take lesson

Advanced Database – Create, modify, delete a one-to-one relationship between tables

The Place For Free Online Training Courses

For this section we need to create a new table. Create a new table in design mode, with the two fields shown for the cost of repair jobs. For the Cost field, select Decimal data type, set decimal places to … Take lesson

Advanced Database – Create, modify, delete a one-to-many relationship

The Place For Free Online Training Courses

A one-to-many relationship links the primary key to a field in a second table that may have more than one records (row). Click Tools – Relationships from the main window ribbon. The Relation Design window opens. Click ‘Insert’ – Add … Take lesson

Advanced Database – Create, modify a many-to-many relationship using a junction table.

The Place For Free Online Training Courses

For this example, create 3 new tables with the designs shown. Note the Order Details table has 2 fields combined to create the primary key. To do this, hold down Ctrl key, click in the grey area to the left … Take lesson

Advanced Database – Apply referential integrity between tables

The Place For Free Online Training Courses

Referential integrity means controlling how records are altered or deleted in a relational database (i.e. one with links/relationships between fields in different tables). To define the rules of how records are altered across relationships, you must right click on the … Take lesson

Advanced Database – Apply automatic update or deletion of related fields.

The Place For Free Online Training Courses

Open the Relation Design window again, this time change both Update options to ‘Update cascade’ for both lines. Update cascade – automatically updates any related foreign keys to match the primary key. Also set the Delete options to ‘Delete cascade’. … Take lesson

Advanced Database – Apply, modify an inner join

The Place For Free Online Training Courses

Joins are links between table that are used to modify the way queries select records. To demonstrate, create a new query in design view. Add the tables ‘Car and owner details’ and ‘Jobs’ In the Field drop down menus, select … Take lesson

Advanced Database – Apply, modify an outer join

The Place For Free Online Training Courses

An outer join allows you to display ALL records from one table, but only records in the other table that match the related fields. You can choose between a Left outer join or Right outer join in the Type drop-down … Take lesson

Advanced Database – Apply, modify a subtract join

The Place For Free Online Training Courses

For this example we need to add a record to the ‘Car and owner details’ table with a Car Reg Number that doesn’t appear in the ‘Jobs’ table. Open the Car and owner details table and add a new record … Take lesson

Advanced Database – Apply a self join

The Place For Free Online Training Courses

For this example, we need to create a new table with the properties and data shown. A self join links a table to itself. In the employee table for instance, if we want a query to list each employee and … Take lesson

Queries in LibreOffice Base compared to MS Access

The Place For Free Online Training Courses

Many of the ‘action’ queries that we are going to look at in this section differ in the way that they are executed between MS Access and LibreOffice Base. Queries that directly update, add to, append or delete records in … Take lesson

Advanced Database – Create, run a query to update data in a table.

The Place For Free Online Training Courses

Open the Order Details table. If we notice that there is an error with certain data, sometimes it is easy to change it using the ‘Update’ command. Imagine we notice that Part Ref ‘D1’ (spark plugs) can only be bought … Take lesson

Advanced Database – Create, run a query to add records to a table.

The Place For Free Online Training Courses

To add records to a table in Base you must use an SQL statement, using the command INSERT. Go to Tools->SQL and in the SQL Command box type: INSERT INTO “Jobs” VALUES (’18’, ‘105’, ‘Repair handbrake’, ’17th JAN 17′, ‘RS1’) … Take lesson

Advanced Database – Create, run a query to append records to a table.

The Place For Free Online Training Courses

Appending records means taking them from one table and putting them into another. In MS Access there is a built-in function to do this, but in Base you will have to use an SQL query. Imagine we want to create … Take lesson

Advanced Database – bound control properties

The Place For Free Online Training Courses

Apply, remove bound control properties like: limit to list, distinct values. Limit to list and distinct values options are only available in MS Access, not Libreoffice Base. Limit to list changes the properties of a combo box to only allow … Take lesson

Advanced Database – Create, run a query to delete records in a table.

The Place For Free Online Training Courses

Imagine we wanted to delete the last 3 jobs from the ‘Jobs’ table as the customers cancelled last minute. We could go through and manually delete them, but imagine if we had hundreds of records to delete (maybe we archived … Take lesson

Advanced Database – Create, run a query to save selected data as a new table.

The Place For Free Online Training Courses

Imagine we want to copy all of the records with a job number of 10 and above from the ‘Jobs’ table into a new table called ‘Recent Jobs’ Of course, we could do this manually by creating a new table … Take lesson

Advanced Database – Create, run a crosstab query

The Place For Free Online Training Courses

A crosstab query involves creating a cross join between two tables, where every record of the left table is combined with every record in the right-hand table. Create a new query in design mode and add the tables Orders and … Take lesson

Create, run a query to show duplicated records within a table.

The Place For Free Online Training Courses

There is no easy way to do this in LibreOffice Base. We have to use a roundabout solution. First create a duplicated record by adding a record to the Mechanics table, repeating the first name and grade under a different … Take lesson

Advanced Database – Create, run a query to show unmatched records in related tables.

The Place For Free Online Training Courses

Again, there is no automatic way to do this in Base, unlike Access. The only way to do it is to use a subtract join which we covered in the relationships section. As a reminder, let’s set a new one … Take lesson

Advanced Database – Create, modify, run a one variable parameter query.

The Place For Free Online Training Courses

A parameter is a value for a certain field, often used as a query selection, that can be changed each time the query is run. Start by creating a new query in design mode and adding the ‘Jobs’ table. Select … Take lesson

Advanced Database – Create, modify, run a two variable parameter query.

The Place For Free Online Training Courses

To add a second variable, open up the same query as we used in the last example. In the third column, choose Staff Number field and type :Staff into the Criterion section. This will set up a second parameter for … Take lesson

Advanced Database – Use wildcards in a query

The Place For Free Online Training Courses

We have already used the asterisk wildcard * in queries to display all fields. The other most common wildcard is ‘?’ which was covered in module 1 and replaces exactly one character. For instance if we run a query to … Take lesson

Advanced Database – Show highest & lowest range of values in a query.

The Place For Free Online Training Courses

First, we are going to try and display the 3 highest priced jobs. Create a new query and add the table Job Costs In the first column display all fields *. In the second column choose the Cost field and … Take lesson

Advanced Database – Create and name a calculated field that performs arithmetic operations.

The Place For Free Online Training Courses

For this example we are going to create a new table called ‘Job Hours’ Create and save it with the data shown (NB. Set decimal places to 2 for the Normal and Overtime Hours) Create a new query and add … Take lesson

Advanced Database – Group information in a query using functions.

The Place For Free Online Training Courses

Add a ‘Staff Number’ field to the Job Hours table and fill in the Staff Numbers from the jobs table. We could have used a join to link the Staff Number field between the two tables, but grouping information in … Take lesson

Advanced Database – bound controls: text box

The Place For Free Online Training Courses

Create, modify, delete bound controls: text box We are going to use the form ‘Jobs Entry’ that we created in an earlier lesson. Right click on Jobs Entry form and select ‘Edit’ to open design view. Hold CTRL and right … Take lesson

Advanced Database – bound controls: combo box

The Place For Free Online Training Courses

Create, modify, delete bound controls: combo box Replace the Staff Number entry box with a Combo Box. A combo box allows you to select from a list or manually enter data. Right click and select ‘Control’ You have the options … Take lesson

Advanced Database – bound controls: list box

The Place For Free Online Training Courses

Create, modify, delete bound controls: list box A list box is similar to a combo box, but you cannot enter values manually. They are a good option if you want to tightly control which values can be entered for a … Take lesson

Advanced Database – bound controls: check box

The Place For Free Online Training Courses

Create, modify, delete bound controls: check box For this example, we need to set up a new form. Start the wizard and create a form from ‘Car and owner details’ including all the fields. Then open this in design mode … Take lesson

Advanced Database – bound controls: option group

The Place For Free Online Training Courses

Create, modify, delete bound controls: option group Open the car and owner detail form in design mode. We are going to create options for the title field to save time typing them out. The options are going to be Mr, … Take lesson

Advanced Database – sequential tab order

The Place For Free Online Training Courses

Modify sequential tab order of controls on a form. You may need to change the tab order of a form, for instance if you add a new response box or want to give certain fields priority over others. Right click … Take lesson

Advanced Database – linked subform

The Place For Free Online Training Courses

Create, delete a linked subform. A subform allows you to display associated data from a different table within a form. Create a new form using the wizard. On the first step, add all of the fields from the table ‘Jobs’, … Take lesson

Advanced Database – Format arithmetic calculation controls in a report

The Place For Free Online Training Courses

We are going to use the query that we created in a previous tutorial. The query is shown to the left, it calculates the total hours worked on each job for each member of staff. Then the query calculates and … Take lesson

Advanced Database – Apply a running sum for a group, over all.

The Place For Free Online Training Courses

In Libreoffice Base, there is no automatic option to do this in a report. We must first create a query that we will then use to run a report. Create a new query in design view. Add the table Job … Take lesson

Advanced Databases – Concatenate fields in a report.

The Place For Free Online Training Courses

Concatenate fields means to merge them together. There is no option to do this automatically in Base, so we first have to create a query in design mode. Add the Car and owner details table. We want to merge First … Take lesson

Advanced Databases – Insert, delete a data field in group, page, report headers and footers.

The Place For Free Online Training Courses

Create a report in wizard mode, select the table Car and owner details and add the first 5 fields only. Click Next twice. Add grouping levels for Surname and First Name. Click Next twice. Choose ‘Columnar, two columns’ layout. Click … Take lesson

Advanced Databases – Sort, group records in a report by field(s)

The Place For Free Online Training Courses

To change the sorting and grouping in a report, click the sorting and grouping icon. In the window that pops up, you can select the grouped fields and change their properties. You can choose to sort ascending or descending, as … Take lesson

Understanding Databases – Force page breaks for groups in a report

The Place For Free Online Training Courses

Select the Surname group section on the left hand side (in blue) In the properties section on the right hand side, where it says ‘Force New Page’ select ‘Before Section’. This will insert a page break before each new section. … Take lesson

Advanced Database – Link external data to a database: spreadsheet

The Place For Free Online Training Courses

For this tutorial, first we are going to create a LibreOffice Calc spreadsheet. Open a new Calc file. In sheet 1, enter the data shown to the left. Then right click on the tab for the sheet and select ‘Rename … Take lesson

Advanced Database – Link external data to a database: text

The Place For Free Online Training Courses

For this tutorial, we are going to create a text file in LibreOffice Writer. Open LibreOffice Writer. Enter the text shown. The first line designates field names, separated by commas. Each new line represents a new record with fields in … Take lesson

Advanced Database – Link external data to a database: existing database

The Place For Free Online Training Courses

To link data from a different database source, open a new LibreOffice Base file. In step 1 of the wizard, choose ‘Connect to an existing database’ and select the format of the database e.g. Microsoft Access 2007. Click Next and … Take lesson

Advanced Database – Import from existing database into a database.

The Place For Free Online Training Courses

Open two separate database files. To import data, you can simply drag and drop between the windows. For example, to copy the Names table from the lower database in the example shown, left click and hold on ‘Names’ in the … Take lesson

Post navigation

← Previous Post


  • Terms and Conditions
  • Privacy Policy
  • Site Map
©2021 - The Place For Free Online Training Courses Privacy Policy
↑