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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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