Advanced Database
Advanced Database – bound controls: text box
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. Hold CTRL and right click on the box next to Staff Number, then select ‘Replace with’ and click ‘Text…
Read MoreAdvanced Database – Group information in a query using functions.
Add a ‘Staff Number’ field to the Job Hours table and fill in the Staff Numbers from the jobs table. 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…
Read MoreAdvanced Database – Create and name a calculated field that performs arithmetic operations.
For this example we are going to create a new table called ‘Job Hours’ 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…
Read MoreAdvanced Database – Show highest & lowest range of values in a query.
First, we are going to try and display the 3 highest priced jobs. Create a new query and add the table Job Costs 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…
Read MoreAdvanced Database – Use wildcards in a query
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. For instance if we run a query to find Car Reg No with the Criterion LIKE ‘RS?’ it will return any car reg…
Read MoreAdvanced Database – Create, modify, run a two variable parameter query.
To add a second variable, open up the same query as we used in the last example. 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…
Read MoreAdvanced Database – Create, modify, run a one variable parameter query.
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. 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.…
Read MoreAdvanced Database – Create, run a query to show unmatched records in related tables.
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. 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…
Read MoreCreate, 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 MoreAdvanced 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