Author: paul.gray

Create, modify, delete bound controls: list box

A list box is similar to a combo box, but you cannot enter values manually.

They are a good option if you want to tightly control which values can be entered for a certain field.

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

Open the Jobs Entry form in design mode by right clicking and selecting Edit.

CTRL right click on the Car Reg No input box, select ‘Replace with’ and choose List box.

Right click and select ‘Control’ and click the Data tab.

The Type of list contents defaults to ‘Valuelist’ for a list box.

This means we can enter the list of values manually by clicking list content and writing each value in quotation marks, pressing SHIFT + ENTER to go to the next value (as we did in an earlier lesson).

[the_ad id=’12397′]

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.

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

Right click and select ‘Control’

You have the options to change the empty string result or set input required, but with a combo box you can also define the contents of the list.

Select ‘Type of list contents’ dropdown menu and choose ‘Table’. We could select SQL and write a statement for what follows, but it’s easier this way.

Select ‘List content’ dropdown menu and click the ‘Mechanics’ table.

This sets the combo box up so the list is going to be generated from the matching field in the Mechanics table, which is what we want.

Click save and close.

Open the form in entry mode by double clicking it.

If you click on the list arrow in the combo box next to staff number, you will see a list of values to choose from that correspond to the Staff Numbers in the Mechanics table.

As it is a combo box, we can also enter a value manually by typing it in.

Close the window.

Another way to select the list contents is to choose a query or to use an SQL statement.

Go to the Control properties for the combo box and select ‘Sql’ for the type of list contents.

Press the ‘…’ symbol next to List content and type: SELECT “Staff Number” FROM “Mechanics”.

This has the same effect as the last example, generating the list from the Staff Number field in the Mechanics table.

[the_ad id=’12397′]

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