Category: Advanced Database

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 and entering the data records one by one.

This is time consuming, so we prefer to use an SQL statement to do the job for us.

[the_ad id=”12355″]
[the_ad id=”12356″]

Select Tools->SQL…

Type in the command: SELECT * INTO “Recent Jobs” FROM “Jobs” WHERE “Job No”>=10

This selects * fields (wildcard to represent all fields, rather than typing them all out) into a new table called ‘Recent Jobs’, which is automatically created, taking all records from ‘Jobs’ tables where the job number is greater than or equal to 10.

Click Execute.

Close the SQL window. Click on the Tables section of the main window. You may notice that there is no new table called ‘Recent Jobs’.

We first have to select View-> Refresh tables in order to see the newly created table.

Open the new table by double clicking in the Tables section.

The SQL statement we executed has copied all of the records with a Job No of 10 and above and placed them into the new table.

If you check the Jobs table, the records will still be present in there.

If you wanted to use this as an archive tool for instance, you could include a delete command in the SQL statement, or in a separate one, to remove the records from the original table.

In general, to save selected data as a new table, use the SQL syntax:

SELECT columns INTO new_table
FROM old_table
WHERE some_condition

[the_ad id=’12397′]

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 them somewhere else). This would take a long time.

[the_ad id=”12355″]
[the_ad id=”12356″]

We can use the DELETE SQL command instead. Select Tools->SQL…

We want to delete records 19,20,21 from the Jobs table.

Type: DELETE FROM “Jobs” WHERE “Job No”>18

This means it will delete from the selected table ‘Jobs’ all of the records that have a Job No greater than 18, i.e. 19,20,21

Click execute to run the query.

Close the SQL window and open the Jobs table.

Notice that records containing job numbers 19, 20 and 21 have now been deleted.

Let’s say we wanted to delete record 10, we could just use the statement: DELETE FROM “Jobs” WHERE “Job No“=10

If we want to delete records less than or equal to 6, use: DELETE FROM “Jobs” WHERE “Job No“<=6 This would delete rows 1,2,3,4,5 and 6

In general, to delete records from a table use the SQL syntax:

DELETE FROM name_table
WHERE some_condition

[the_ad id=’12397′]

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.

[the_ad id=”12355″]
[the_ad id=”12356″]

Limit to list changes the properties of a combo box to only allow values from the list.

To do this in Base, you simply need to select the combo box, right click and select Replace with -> List Box

Then enter or link to values in the list box as shown in a previous lesson.

[the_ad id=’12397′]

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.

[the_ad id=”12355″]
[the_ad id=”12356″]

Imagine we want to create an archive system for completed jobs.

To start with, create a new table called New Jobs which has the same fields as the Jobs table.

Open the New Jobs table and add 2 new records.

We are going to append these records into the Jobs table using an SQL statement

Close the table and select Tools->SQL… to open the SQL statement window.

Type in the command:

INSERT INTO “Jobs” ( “Job No”, “Staff Number”, “Job Description”, “Date”, “Car Reg No”) SELECT “Job No”, “Staff Number”, “Job Description”, “Date”, “Car Reg No” FROM “New Jobs“;

This will insert the selected fields from New Jobs into Jobs.

Note the semi-colon (;) at the end. This means it will select all records to append. Without it, only the first record is selected.

Execute the command then close the SQL window.

Open the Jobs table and notice that jobs 20 and 21 have been copied from New Jobs into Jobs.

If you open the New Jobs table, the two records 20 and 21 are still in there. For this to work as an archive system you need to delete them too.

The next section teaches you how to delete records using a query.

In general, to append records to a table use the SQL syntax:

INSERT INTO name_table ( column1, column2, …)
SELECT column1, column2, … FROM name_table2 ;

[the_ad id=’12397′]

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

[the_ad id=”12355″]
[the_ad id=”12356″]

Notice that the command inserts a record that includes all 5 fields in the same order they appear in the ‘Jobs’ table.

Click Execute & check Status.

In general, to add a record use the SQL syntax:

INSERT INTO name_table (column1, column2, column3….)
VALUES (value1, value2, value3….).

Close the SQL statement window and open the Jobs table.

Notice that a new record has been added to the table.

[the_ad id=’12397′]

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.

[the_ad id=”12355″]
[the_ad id=”12356″]

Imagine we notice that Part Ref ‘D1’ (spark plugs) can only be bought in packs of 10, but many records show less than 10 – a human error.

We can correct this using the UPDATE command in SQL. Close the table.

Select Tools -> SQL from the main ribbon to open ‘Execute SQL Statement’ window.

In the Command to execute section type: UPDATE “Order Details” SET “Amount”=’10’ WHERE “Part Ref”=D1

Note that SQL uses colour codes: Commands are blue. Table names, fields and records are orange. Mathematical operators are black. Also, use “” to refer to tables and fields and ‘’ to refer to text values.

This command will update the Order Details table setting the amount field to 10 whenever there is a D1 in the Part Ref field.

Click ‘Execute’ to run the query.

Status will state ‘Command successfully executed.’

Close the execute SQL window.

Open the Order Details table.

Notice that Order Ref 210 amount has changed from 3 to 10 to reflect the update we performed.

This is a useful feature to use if you have large amounts of data that needs changing. For example, a store raising all of its prices by 10% could use the update function to quickly change records.

In general, to update a record in a table use the SQL syntax:

UPDATE name_table
SET name_column=value
WHERE some_condition

NB. some_condition is often a mathematical statement such as =8 or >15 or it could be applied to a text field (column) =‘Yes’, for instance.

[the_ad id=’12397′]

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 a table can be done without using SQL code in MS Access, by using certain tools.

However, in Base there are no available tools, so we have to use some basic SQL commands.

SQL may seem confusing at first, but is actually very straightforward and intuitive when you get used to it. We will talk you through how each SQL statement works and you can use the glossary as a reference too.

[the_ad id=”12397″]

For this example, we need to create a new table with the properties and data shown.

[the_ad id=”12355″]
[the_ad id=”12356″]

A self join links a table to itself.

In the employee table for instance, if we want a query to list each employee and the name of who they report to, we would need a self join.

Let’s first try without a self join. Set up a new query, adding the Employees table twice. Notice it automatically names the second table ‘Employees_1’.

Select the fields shown to the left and press F5 to run the query.

There are a couple of problems with this query. First of all it repeats each staff member several times. Also it displays the staff number, not name of the person they report to.

We can solve this by setting up a self-join.

Press F4 to remove the table.

To apply a self join, decide which fields we want to connect with an inner join.

In this case we want the staff no given in the ‘Reports to’ field of the primary table to relate to the ‘Staff No’ field of the duplicate table.

Therefore, drag a join from ‘Reports to’ to ‘Staff No’ as shown.

Notice we also change the third displayed field to ‘Name’ from ‘Employees_1’. Press F5.

This time it displays the query table exactly the way we want, with the name of the manager instead of the staff no, and no repeated records.

To modify or delete the join, simply right-click the connecting line and select Edit or Delete.

Press F4 to close the query results.

[the_ad id=”12397″]

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.

[the_ad id=”12355″]
[the_ad id=”12356″]

Open the Car and owner details table and add a new record at the bottom of the table. Don’t add anything to the jobs table.

Close the table and reopen the query we used in the last example.

A subtract join is the opposite of an outer join, i.e. it includes only results in one table that don’t match any records in the other.

Base doesn’t have a direct option in design mode to do this, so it’s best to use some simple SQL.

To toggle into SQL editing mode, click the ‘Switch Design View On/Off’ icon.

Delete any text that is already in the SQL editor.

Then enter the text exactly as it appears in the image.

This SQL code sets up a left outer join between Car and owner details table and Jobs table, listing all the fields for any records that have a Car Reg Number in ‘Car and owner details’, that does not appear in the ‘Jobs’ table (IS NULL).

Press F5 to run the query

Notice that the query only returns the one record that is in the Car and owner details table, but not in the jobs table.

To see how this can be created without SQL code, click the Switch Design View On/Off icon

Don’t worry too much about the SQL commands for now, we’ll discuss these in more detail later on in the course.

Right click the connecting line and select edit to open join properties.

The SQL code we entered has changed the join type to a Left join. It can be modified here.

Notice in the query table, it has set the Criterion to ‘IS EMPTY’ which was the ‘IS NULL’ part of our SQL code.

This is a valid way of creating the same query without SQL.

[the_ad id=”12397″]

An outer join allows you to display ALL records from one table, but only records in the other table that match the related fields.

[the_ad id=”12355″]
[the_ad id=”12356″]

You can choose between a Left outer join or Right outer join in the Type drop-down menu.

Choosing Left join will display all records from the left-hand table, Right will display all from the right-hand table.