Common Database Tasks
Tables
Tables are used to store records (pieces of data) within a database.
Records are entered into tables under fields that are created when editing the table. Fields are similar to the headings given to columns in a spreadsheet.
An unlimited number of tables can be created in a database, containing different types of data.
For example, a shop may keep records of stock, employees, sales, revenue, etc. all in the same database, but in various tables with different titles.
Open, save and close a table
To open a table in an existing database, click on ‘Tables’ in the ‘Database’ area of the main window.
Then double-click on one of the tables in the ‘Tables’ area.
If you make changes to any records in the table, click the disc icon to save it.
To close the table, click the cross in the upper right corner of the table window.
Queries
Queries are a method of extracting data from the tables within a database. Data can be extracted from more than one table at a time using a query, as long as the tables have a relationship created between them.
For instance, a company may use a query that searches through employee data to find out who is paid the most, or which employees are paid more than a certain amount.
There are 3 ways to create queries in LibreOffice Base. They are design view, wizard and SQL (Structured Query Language). SQL is the most advanced of these and the most versatile.
Open, save and close a query
To open a query, first click on ‘Queries’ in the ‘Database’ area of the main window.
Then double click the relevant query in the ‘Queries’ area to open it.
Forms
A database form is a user interface that helps with data entry and maintenance.
For instance, an engineering company may use a database to log faults with machinery. They can create a form that allows engineers to enter relevant details of the fault in a clear, well laid out form, rather than trying to enter them directly into a table which is more difficult and a slower process.
Forms can also be used to allow a quick view of records within a table. They can be displayed in order, one record at a time, or filtered to show records with specific data only.
A form could be described as a window into a database where people can view and edit records in a simple way. Changes to data have to be saved before exiting forms.
Open, save and close a form
To open a form, first click on ‘Forms’ in the ‘Database’ area of the main window.
Then double click on the relevant form in the ‘Forms’ area.
To save the form click the disc icon to ‘Save as’.
Choose ‘Save copy as’ then select a location and name the file.
Reports
A database report is a formatted display of records from a table or the results of a query.
Reports provide users with information that can help decision making or analysis, in an easy-to-view format.
For example, a bank could use database reports to list monthly loan summaries for customers. They can use the report to highlight which customers are behind on their repayments. They could break down the data further by creating reports linked to queries that break down the number of customers that are behind on payments for large loans, compared to those for smaller ones.
Output parameters and restrictions can be applied to reports, as well as grouping data so that it isn’t repeated. This is explained in more detail later on in the course.
Open, save and close a report
To open a report Click on ‘Reports’ in the ‘Database’ area of the main window.
Then double click on the relevant report in the ‘Reports’ area.
The report will open in a LibreOffice Writer window.
To save the report as a text document, click the ‘Save’ icon and name the file and save to a specified Folder.
To close the report document, click the cross in the top right corner of the window
