Category: Advanced Database

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.

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

Hold CTRL and right click on the box next to Staff Number, then select ‘Replace with’ and click ‘Text Box’.

Right click again and select ‘Control’ to open the control properties box.

The controls are limited for a text box. Click the Data tab.

‘Empty String is NULL’ controls the SQL result. If Yes, then when empty it returns NULL result. If No, then no result is returned when box is empty.

It’s generally best to leave this set to Yes, unless you are using complex SQL statements.

You can also set Input required to Yes or No. If set to Yes, then a record cannot be saved until a value is entered.

[the_ad id=”12397″]

Add a ‘Staff Number’ field to the Job Hours table and fill in the Staff Numbers from the jobs table.

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

We could have used a join to link the Staff Number field between the two tables, but grouping information in a query doesn’t work with joined tables.

Create a new query and add the Job Hours table.

Select to display the Staff Number field and select ‘Group’ function i.e. where there is more than one record containing a certain value it will group them into one.

Select to display the Alias field Total Hours (Normal+Overtime) and select ‘Sum’. This will take each staff number and find the sum of the Total Hours field for each. Press F5.

The query table returns the sum of the Total Hours field for each staff number, grouped together.

Try changing the function in the second column to ‘Average’ and press F5.

The query table now returns the average number of total hours each staff member worked.

Try changing the function in the second column to ‘Maximum’

The query table now returns the maximum number of total hours each staff member worked.

If we had used the ‘Minimum’ function it would have returned the least number total number of hours worked for each staff number.

For the last example, change the Alias to ‘Number of Jobs’ and change the function to ‘Count’. Press F5.

The query table now returns the total number of jobs each staff member worked.

The Count function, counts each unique record that exists for each Staff Number and returns the total value.

[the_ad id=’12397′]

For this example we are going to create a new table called ‘Job Hours’

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

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 the new Job Hours table.

In the first column select the all fields *.

In the second column we are going to create a new field which adds together the Normal Hours and Overtime Hours fields.

In the Field box type “Normal Hours” + “Overtime Hours” and in the Alias box type Total Hours. Make sure the Visible box is checked and press F5.

Notice that the query table displays the new field that we created with the alias “Total Hours”

You can perform other arithmetic operations such as subtracting, multiplying and dividing using the same method.

This can only be used on numerical fields.

[the_ad id=’12397′]

First, we are going to try and display the 3 highest priced jobs.

Create a new query and add the table Job Costs

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

In the first column display all fields *. In the second column choose the Cost field and select ‘descending’ in the Sort box.

Press F5 to run the query and notice that it displays all of the jobs in descending order of cost.

In order to show only the top 3 records, switch into SQL view.

It will already have the query we created in design mode i.e: SELECT * FROM “Job Costs” ORDER BY “Cost” DESC

Type immediately after DESC: LIMIT 3

This will limit the results to only 3 records. Press F5 to run the query again.

This time it only displays the highest 3 costs, as required.

If instead we want to show the jobs with the lowest 5 costs, we can edit the SQL again.

Change the SQL to: SELECT * FROM “Job Costs” ORDER BY “Cost” ASC LIMIT 5

Press F5 to run the query.

his time it displays the records with the lowest 5 prices.

[the_ad id=’12397′]

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.

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

For instance if we run a query to find Car Reg No with the Criterion LIKE ‘RS?’ it will return any car reg number that begins with RS and has one more character after it.

The wildcard * can be useful when to find any data that contains a specific character.

Create a query and add the car and owner details table. We are going to show all that have a first name with the letter ‘h’ in it.

In the first column select all fields using the * wildcard, in the second column select first name field and type LIKE ‘*h*’ in the criterion box.
Press F5 to run the query.

The query displays the two records that have the letter ‘h’ in the middle of the name, but ‘Hans’ doesn’t display as it is at the beginning.

To make sure that the query searches for the initial ‘H’ too, we’ll adapt the criterion.

Press F4 to close the query table.

In the OR box below the criterion, type LIKE ‘H*’

Now the query will return First Names that begin with H as well as contain h within them.

Press F5 to run the query and notice that Hans is now included in the query table.

Press F4 to close the table and switch the view into SQL mode.

For some reason, Base displays wildcards differently in SQL than in the design view.

The * wildcard displays as % and the ? Displays as _

The other wildcards [ ], !, -, # are only used in MS Access, and there are no equivalents in Libreoffice Base.

[the_ad id=’12397′]

To add a second variable, open up the same query as we used in the last example.

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

In the third column, choose Staff Number field and type :Staff into the Criterion section.

This will set up a second parameter for the staff number.

Notice that Visible is unchecked for both the Car Reg No and Staff Number. This prevents these fields from being repeated in the query table. Press F5 to run query.

This time, there are two parameters to input.

Enter a Car Reg No (e.g. GF54BLL) then click on Staff or Next and enter a Staff Number (e.g. 109)

The query table returns only the records that contain the two variables inputted.

Two is the maximum permitted number of parameters in a query.

Parameter queries are useful for queries that you run regularly, where the only thing changing is the selection criteria for one or two fields.

[the_ad id=’12397′]

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.

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

Start by creating a new query in design mode and adding the ‘Jobs’ table.

Select to display all fields from Jobs using the * wildcard in the first query column.

In the second, choose Car Reg No field then input :Car into the Criterion section.

The semi colon (:) designates a parameter.

If you switch to view it in SQL mode, notice that the :Car is displayed in a light blue car to represent a parameter.

Save the query and give it a name.

Go back to design view and press F5 to run the query.

As it is a parameter query the system expects a specific value for the Car Reg No field, so a dialogue box for Parameter Input will open up.

Type in one of the car registration numbers that is included in the table and click OK.

The query displays all of the records that have the Car Reg No you inputted as the parameter.

If you run the query again, it will reset the parameter and you can enter a new value.

This is a more sophisticated way of searching a table that can be combined with other query commands.

[the_ad id=’12397′]

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.

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

As a reminder, let’s set a new one up.

Open the Mechanics folder and add a new staff member.

Let’s say we want to display the mechanics that haven’t worked on any jobs

Create a new query and add tables Mechanics and Jobs.

Create a Right join between the Staff Number fields.

In the first column show all fields from Mechanics, in the second column select the Staff Number field from Jobs with the Criterion ‘IS EMPTY’ (uncheck visible as we don’t need to see the empty record)

Press F5 to run the query.

The query will return any records from the Mechanics table that have unmatched staff numbers in Jobs table.

[the_ad id=’12397′]

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