Understanding Databases – Present Fields by Average and Count
In LibreOffice Base, the simplest way to present specific fields using average or count functions is to create a query first.
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.
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.
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