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

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 staff number. This could happen in real-life as an admin error. Close the table. Create…

Read More

Advanced Database – Create, run a crosstab query

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 Parts. Select to display all fields from both tables. Create a Cross join by dragging…

Read More

Advanced Database – bound control properties

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 values from the list. To do this in Base, you simply need to select the…

Read More

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

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’) Notice that the command inserts a record that includes all 5 fields in the same…

Read More

Queries in LibreOffice Base compared to MS Access

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…

Read More