Author: paul.gray

In LibreOffice Base, the simplest way to present specific fields using average or count functions is to create a query first.

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

Average will return the mean average of the field, therefore it requires numerical data. Mean average is calculated by finding the sum total of all the records, divided by the count (the number of records there are). The average function does this for you automatically.

An example of where the ‘average’ function may be used is if you’re using a database to record results of football matches. You can set the average function to find the average number of goals scored per match for a given team, for example.

The ‘count’ function will display a number representing the total number of records containing data in that field.

E.g. if there are 25 records containing data, in the result of a query for a specified field, the ‘count’ of that field will return the value 25. Therefore, the data in this field doesn’t have to be numerical, it can take any form.

You can also choose to count ‘null’ values, i.e. records where there is no data present for the specified field.

An example of where this could be used is a report to count the number of customers that sign up for a loyalty card in a shop. You can set it to only count records where the ‘sign up date’ field is filled in, for example.

Design a new query and add the tables and fields shown.

Group Last Name, First Name, employee-ID and Salary, so none of these are repeated.

Add two Bonus fields and select ‘Count’ and ‘Average’ from the Function drop-down menus. Then save the query as ‘average bonus’.

Now create a report using the wizard and use the ‘average bonus’ query.

Add all of the fields from the query.

Click Next.

Relabel the COUNT and AVG fields to more descriptive labels

As the query is already grouped, there’s no need to group the report.

Choose Sort by ‘Last Name’ – Ascending, to sort the report in alphabetical order.

Choose the preferred layout and click Finish to create the report.

A report will open as a document, displaying the count and average for the bonus fields in alphabetical order by name.

Once you have closed the report, if you wish to run it again, simply double click the report from the main window.

To edit the report, right click it in the main window and select edit.

[the_ad id=”12397″]

Related lessons

In this lesson, we learn about the various database functions available in the ‘Function Wizard’ of Microsoft Excel. We explore the ‘DSUM’ function and its three arguments: ‘Database’, ‘Database field’, and ‘Search criteria’.
Advanced Spreadsheets – Database Functions

Present specific fields by maximum or minimum

You can present fields in a report using maximum or minimum functions, again without the need for a query.

The function is applied directly to the field to either display the minimum or maximum value from the records. Therefore, it can only be used on numerical values.

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

An example where this could be used is in a financial or accounting report. For instance, you could display the maximum or minimum sales figures for a given time frame.

From the main window, right click the report and select edit.

Create a label and text box for the specific field you want to find the maximum or minimum of e.g. Maximum Bonus.

Double click the text box or select Properties from the toolbar.

In the properties window, select the ‘Data’ tab.

Change Data Field Type to ‘Function’.

Select the specific Data Field, e.g. bonus.

From the ‘Function’ drop-down menu select ‘Maximum’ or ‘Minimum’.

The data will be displayed with the maximum or minimum value in the specified box.

In this example, the maximum bonus from the bonus field is displayed.

[the_ad id=”12397″]

Present specific fields in a grouped report by sum

The ‘sum’ function will add together all the records in a specified field.

Therefore, the sum function can only be applied to numerical fields.

Reports allow you to present fields as a sum without having to create a query to include the function.

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

An accounts database is an example of where this might be useful. Total monthly sales revenue can be displayed in a report by using the sum function simply by applying it to a ‘takings’ or ‘daily revenue’ field.

To present a specific field as a sum, first open the report in design mode.

Then click on the ‘Label’ icon on the toolbar, and draw a text box in a grouped section.

To change the text in the label, double click the text box, or select properties.

In the Label section of properties, type the text you want to display e.g. ‘Total Bonus’.

Then click the ‘Text Box’ icon from the toolbar and draw a text box next to the lable.

Double click the text box or select properties.

Select the ‘Data’ Tab from the properties window.

To present fields by sum, select the ‘Function’ option from the Data Field Type drop-down menu.

Select the field you want to display the sum for, from the Data Field drop-down menu e.g. Bonus.

From the Function drop-down menu, choose ‘Accumulation’.

From the Scope drop-down menu, choose the group you are displaying the data in.

Execute the report to open the text document.

The text box created will now be displayed as a sum of the selected field.

In this case, all of the bonus values are added together to give a total bonus value for each individual employee record.

[the_ad id=”12397″]

Present specific fields in a grouped report

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

For this section, create a new table to display salary and another to display bonuses.

This data will require a grouped report to display correctly as there are multiple entries for employee-ID.

Next, we need to create a relationship, in the main window, click ‘Relationships’ from the ‘Tools’ menu.

Click ‘Add Tables’ from the ‘Insert’ menu. This will give you the option to select a table and add the newly created tables.

To make the relationship, click and hold on a field in one table, then drag across to the one you want to create a relationship with in another table.

Lines symbolising the links will join the two fields.

Right click the joining line and click edit.

Select ‘Update cascade’ and ‘Delete cascade’ from the Relations menu.

Click OK, then save the relationship and close the window

Next create a query in design view.

Add the tables you want to use in the query (in this case Employee Basic Details and Salary and Bonus).

Add the desired fields from the three tables.

Select sort ascending for last name to get results in alphabetical order.

Click the save icon and name the query.

From the main window, select ‘Use Wizard to create report’.

Select the Query you made earlier.

Add the fields that you want to display in the report.

Click ‘Next’ until you get to step 3-Grouping.

Set up groups for data that you don’t want to be repeated in the report.

For this example, don’t add Bonus and Bonus Date as some people received more than one bonus.

This will display the Grouped fields, followed by each bonus earned, rather than duplicating all of the data.

Click ‘Next’ until 5-Choose layout.

Choose the layout you prefer. For this example, Columnar, two columns is best.

Click ‘Finish’ to display the report.

The report document may look badly formatted.

Notice how fields don’t line up and grouped data splits across pages.

In the main window, right click the report and select Edit.

To align correctly, click each text box individually, then click the ‘align left’ icon from the ‘align’ toolbar.

This will line up all the text boxes to the left.

Click on the horizontal line dividers and press delete. Add one horizontal line above ‘Last Name’ group to divide records.

Click the ‘Sorting and Grouping’ icon from the toolbar.

To stop groups splitting over pages, click each group separately and select ‘Keep Together’ – ‘Whole Group’ from the drop-down menu.

To check the formatting click ‘Execute Report’ from the toolbar to open the report document.

[the_ad id=”12397″]

Either create a new report through the wizard or edit an existing report in Report Design View.

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

To group fields, click on the ‘Sorting and Grouping’ icon on the toolbar.

Grouping fields will stop information from being duplicated, for instance if the same person has a home and mobile number stored in the database.

Select a field name for each group from the drop down menus.

Move the fields that you don’t want repeated up to the header for that group.

Use the horizontal line icon to draw a line above the top field to separate records.

Execute the report to open a text document.

In this example, the report no longer repeats the name and group for records that have both a mobile and home number.

[the_ad id=”12397″]

Create and name a report based on a table or query

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

Begin by clicking ‘Reports’ in the Database area of the main window, then click ‘Use Wizard to Create Report’ from the Tasks area.

In the Report Wizard window, select the desired table or query from the drop down menu.

Select the fields that you want to show in the report by double clicking in the Available Fields menu.

If you want to display them all, click the fast forward button.

Click next when ready to move on. For now, keep clicking until you get to ‘5. Choose layout’

Try selecting different Layouts, such as columnar, in blocks, etc.

Notice how it changes the display in the main report page behind.

This is how your final report will look, so decide on a style you like, then click Next.

Type a name for the report in the dialogue box under ‘Title of report’ and select ‘Dynamic report’ and ‘Create report now’.

Click Finish to run the report, opening a text document in LibreOffice Writer.

The text document will display all the data fields and records from the table or query that you selected.

Notice that in this example some of the records are repeated as they had mobile and home number data in one of the tables.

Soon we will look at creating grouped reports to solve this problem.

[the_ad id=”12397″]

Understand that a report is used to print from a table or query

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

Reports are used to extract data from a table or query, by exporting to a printable text file.

It can be saved and run again if more reports are created or data is modified.

Because database reports simplify and filter the records contained in tables or queries and displays them in an efficient way, they are useful for analysing data.

For example, an airline flight booking database might produce a report to list the names of people on a certain flight.

Running a report like this will remove extra data that may not be relevant to the required report, such as cost of flight, age of customer, etc.

Other reports could be created from the same query, such as a report listing special requirements of passengers, i.e. disabilities, dietary requirements, etc.

Use a form to insert new records

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

To insert a new record, click the ‘New Record’ icon on the form navigation toolbar.

You can enter the data straight into the field boxes on the form.

Note that if you have set the ID to an integer in the table, this will generate the value automatically in increments of 1 for this field.

Once you have finished entering the record, click the disc icon to save the record.

Use a form to delete records

To delete a record, first find the one you want to delete by using the forward and backward arrows in the navigation toolbar.

Alternatively, click on the search icon to find the record by specific field data.

Once you have found the record you want to delete, click the ‘Delete Record’ icon from the toolbar.

Use a form to add, modify, delete data in a record

First, find the record you want to edit by clicking on the Search icon in the toolbar.

Use the menu to search, e.g. ‘single field – Last Name’, Search the ‘beginning of field’ for the text ‘J’ will return records with first letter of surname ‘J’.

Keep clicking ‘Search’ button until you find the record you want e.g. Jones or Jenkins.

Once you have the record you want, click the data you want to add or change.

Type data into the field box to add or modify existing data.

Highlight the data and press delete to remove the data.

Click the disc icon on the form navigation toolbar to save the changes to the record.

Add, modify text in headers, footers in a form.

First open the form in Design view. To do this, right click the form in the main window and select ‘Edit’.

To add text, click on the ‘Text Box’ icon on the Form Design toolbar.

Click and drag on the form where you want the text to go.

Type the text you want to display on the form, e.g. Form title in the header and instructions in the footer.

To modify the text, click inside the text box.

Use the Text Object toolbar to change the font, text size, style, etc.

[the_ad id=”12397″]

The simplest way to create a new form is to use the ‘Wizard’.

Click the ‘Forms’ section in the Database area of the main window and select ‘Use Wizard to Create Form’ from the Tasks area.

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

Use the drop-down menu to choose the table you want to display.

Select the fields you want to display by double clicking them in the ‘Available fields’ section one by one. If you want to display all fields, click the ‘fast forward’ icon.

Keep clicking ‘Next’ until you get to ‘5. Arrange controls’ menu.

Try clicking on the different ‘Arrangement of the main form’ icons until you find a display you like.

You can also change the label placements by clicking align left or right.
Once you’re happy with the layout, click ‘Next’.

If you want the form to be used for entering new data only, with no existing data shown, click the top option.

If you want all data to be shown, click the bottom one.

You can also check or uncheck options to disallow modification, deletion or addition of new data, depending on your needs.

Click Next until you get to the ‘Apply styles’ menu.

Click to change the background colour of the form. You can also change the style of the field border.

Click ‘Next’ when done.

[the_ad id=”12397″]

Name the form by typing in the dialogue box. You can use the same name as the used table if you like (this is the default).

If you want to do some further modifications in ‘Form Design View’ select ‘Modify the form’.

If you want to start using the form select ‘Work with form’ and click ‘Finish’.

Understand that a form is used to display and maintain records.

Forms display records one at a time in a format that can be customized.

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

Rather than reading the data from a table, it can be laid out in a way to suit your needs.
Forms provide a way for users of the database to add or edit data in a simpler fashion than entering directly into a table

An example of a database where a form could be useful is in stock taking.

A table may contain the fields Item Code, Date, Number of units on shelf, Number of units in warehouse.

A simple form can be created for this data, that allows for easy entry of stock inventory.

Bigger businesses tend to use software that automates this process by using barcode scanners that will automatically populate a database, but the principle is the same. Using a form is a cheaper alternative for smaller or growing businesses.