Base Handbook - LibreOffice

Version 4.0

Base Handbook

Copyright

This document is Copyright ? 2013 by its contributors as listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License (), version 3 or later, or the Creative Commons Attribution License (), version 3.0 or later.

All trademarks within this guide belong to their legitimate owners.

Contributors

Jochen Schiffers Hazel Russman Dan Lewis

Robert Gro?kopf Martin Fox Jean Hollis Weber

Jost Lange Andrew Pitonyak

Acknowledgments

This book is based on an original German document, which was translated by Hazel Russman and Martin Fox.

Feedback

Please direct any comments or suggestions about this document to: documentation@global.

Publication date and software version

Published 3 July 2013. Based on LibreOffice 4.0.

Documentation for LibreOffice is available at

Contents

Copyright..................................................................................................................................... 2 Contributors............................................................................................................................. 2 Feedback................................................................................................................................ 2 Acknowledgments................................................................................................................... 2 Publication date and software version..................................................................................... 2

Preface.................................................................................................................................. 7 Who is this book for?................................................................................................................... 8 What's in this book?..................................................................................................................... 8 Where to get more help............................................................................................................... 8 Help system............................................................................................................................ 8 Free online support................................................................................................................. 8 Paid support and training........................................................................................................ 9 What you see may be different.................................................................................................... 9 Illustrations.............................................................................................................................. 9 Icons..................................................................................................................................... 10 Using LibreOffice on a Mac........................................................................................................ 10 What are all these things called?............................................................................................... 10 Who wrote this book?................................................................................................................. 11 Frequently asked questions....................................................................................................... 12

Chapter 1 Introduction to Base.......................................................................................................... 13

Introduction................................................................................................................................ 14 Base ? a container for database content....................................................................................14 Data input using forms............................................................................................................... 15 Data input directly into a table.................................................................................................... 16 Queries ? getting information on data in tables.......................................................................... 17 Reports ? presentation of data................................................................................................... 17

Chapter 2 Creating a Database...........................................................................................................21

General notes on the creation of a database............................................................................. 22 New database using the internal HSQL engine.......................................................................... 22 Accessing external databases................................................................................................... 22 MySQL databases..................................................................................................................... 23 dBase databases....................................................................................................................... 30 Spreadsheets............................................................................................................................. 31 Thunderbird address book......................................................................................................... 32

Chapter 3 Tables.................................................................................................................................. 33

General information on tables.................................................................................................... 34 Relationships between tables.................................................................................................... 34

Relationships for tables in databases.................................................................................... 34 Tables and relationships for the example database............................................................... 37 Creating tables........................................................................................................................... 40 Creation using the graphical user interface........................................................................... 41 Direct entry of SQL commands............................................................................................. 43

Base Handbook

3

Linking tables............................................................................................................................. 48

Entering data into tables............................................................................................................ 52 Entry using the Base GUI...................................................................................................... 52 Direct entry using SQL.......................................................................................................... 57 Problems with these data entry methods...............................................................................59

Chapter 4 Forms.................................................................................................................................. 61

Forms make data entry easier................................................................................................... 62

Creating forms........................................................................................................................... 62 A simple form........................................................................................................................ 62 Toolbars for form design........................................................................................................ 63

Form properties......................................................................................................................... 66 Properties of controls............................................................................................................ 69 A simple form completed....................................................................................................... 95

Main forms and subforms........................................................................................................ 103

One view ? many forms........................................................................................................... 115

Chapter 5 Queries.............................................................................................................................. 121

General information on queries................................................................................................ 122

Entering queries....................................................................................................................... 122 Creating queries using the Query Design dialog................................................................. 122 Query enhancement using SQL Mode................................................................................ 134

Using an alias in a query.......................................................................................................... 141

Queries for the creation of list box fields.................................................................................. 142

Queries as a basis for additional information in forms.............................................................. 143

Data entry possibilities within queries...................................................................................... 143

Use of parameters in queries................................................................................................... 144

Subqueries............................................................................................................................... 144

Correlated subqueries.............................................................................................................. 145

Queries as source tables for queries....................................................................................... 145

Summarizing data with queries................................................................................................ 146

More rapid access to queries using table views....................................................................... 147

Chapter 6 Reports..............................................................................................................................149

Creating reports using the Report Builder................................................................................ 150

The user interface of the Report Builder.................................................................................. 150 General properties of fields................................................................................................. 157 Data properties of fields...................................................................................................... 161

Functions in the Report Builder................................................................................................ 162 Entering formulas................................................................................................................ 162 User-defined functions........................................................................................................ 168 Formula entry for a field...................................................................................................... 169 Conditional print.................................................................................................................. 169 Conditional formatting......................................................................................................... 169

Chapter 7 Linking to Databases....................................................................................................... 171

General notes on database linkage......................................................................................... 172

4

Base Handbook

Registration of databases........................................................................................................ 172

Data source browser................................................................................................................ 172 Data to Text......................................................................................................................... 174 Data to Fields...................................................................................................................... 177

Mail merge............................................................................................................................... 178 Data source of current document........................................................................................ 178 Explorer on/off..................................................................................................................... 178

Creating mail merge documents.............................................................................................. 178

Label printing........................................................................................................................... 185

Direct creation of mail merge and label documents................................................................. 188 Mail merge using the mouse............................................................................................... 188 Creating form letters by selecting fields............................................................................... 189

External forms.......................................................................................................................... 190

Database use in Calc............................................................................................................... 191 Entering data into Calc........................................................................................................ 191 Exporting data from Calc into a database............................................................................ 193

Converting data from one database to another........................................................................ 196

Chapter 8 Database tasks................................................................................................................. 197

General remarks on database tasks........................................................................................ 198

Data filtering............................................................................................................................. 198

Searching for data................................................................................................................... 200

Code snippets.......................................................................................................................... 201 Getting someone's current age........................................................................................... 201 Getting a running balance by categories............................................................................. 202 Line numbering................................................................................................................... 203 Getting a line break through a query................................................................................... 205 Grouping and summarizing................................................................................................. 205

Chapter 9 Macros............................................................................................................................... 207

General remarks on macros.................................................................................................... 208

Improving usability................................................................................................................... 209 Automatic updating of forms................................................................................................ 209 Filtering records.................................................................................................................. 210 Searching data records....................................................................................................... 213 Comboboxes as listboxes with an entry option.................................................................... 215 Navigation from one form to another................................................................................... 225 Removing distracting elements from forms......................................................................... 226

Database tasks expanded using macros................................................................................. 226 Making a connection to a database..................................................................................... 226 Securing your database...................................................................................................... 227 Database compaction.......................................................................................................... 228 Decreasing the table index for autovalue fields................................................................... 228

Dialogs..................................................................................................................................... 229

Chapter 10 Database Maintenance.................................................................................................... 239

General remarks on maintaining databases............................................................................. 240

Compacting a database........................................................................................................... 240

Resetting autovalues............................................................................................................... 240

Base Handbook

5

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download