Author: paul.gray

There is no easy way to do this in LibreOffice Base. We have to use a roundabout solution.

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

First create a duplicated record by adding a record to the Mechanics table, repeating the first name and grade under a different staff number.

This could happen in real-life as an admin error.

Close the table.

Create a new query in design mode.

Add the Mechanics table.

Select to display the Name field with the function ‘Group’. This will sort identical Names into groups.

In the next column, select all fields with the function ‘Count’. Then Press F5 to run the query.

The query results will return a table that shows you how many records there are for each name.

You can quickly see that one name has been duplicated.

We can then retrieve those duplicated records by changing the query.

Change the first column to all fields using the asterisk (*)

Change the second column to the Name field with the criterion ‘Matthew Grove’, i.e. the text that is in the duplicated record.

Press F5 to run the query.

The query now shows the duplicated records.

You can then go to the table or use an SQL command to delete or amend the erroneous record.

[the_ad id=’12397′]

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.

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

Create a new query in design mode and add the tables Orders and Parts. Select to display all fields from both tables.

Create a Cross join by dragging the primary key to the foreign key, then right click the connection click Edit and select ‘Cross Join’ from the menu.

Press F5 to run the query, and you’ll see that it returns repeated records from the Orders table, matched with repeated records from the Parts table.

This is because the Crosstab query creates what is known as a Cartesian product.

The best way to think of this is as a co-ordinate grid, containing the primary key and foreign key from the two tables.

Each cell within the co-ordinate (cartesian) grid contains all the records related to both keys.

So, for instance the Order No 202 when crosstab queried will contain the record data for that order, plus the record data for each of the parts A1-D2.

Thus, in this example the query returns 10×9=90 records.

We can verify this by making a simple amendment to the previous query.

In the function row of the query, select ‘Count’.

This will return the value of how many records there are in the query.

Press F5 to run the query

As predicted, the Count, i.e. the number of records present in the query is 90.

Crosstab queries can be used to display summaries of values such as Sum, Avg, Max and Min instead of count.

For instance, we may want a query to display the earliest order date and the average price of parts.

Use a crosstab query (cross join between Orders and Parts tables)

Select to display Date from the Orders table using the Minimum function.

Select to display the Price from the Parts table using the Average function. Then press F5.

[the_ad id=’12397′]

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