Latest revision as of 14:38, 2 August 2022

Base Tutorial

Building a club membership database

Chapter 1 Table, Querys, Forms

Copyright

This document is Copyright © 2007 by its contributors as listed in the section titled Authors. You can distribute it and/or modify it under the terms of either the GNU General Public License, version 2 or later (https://www.gnu.org/licenses/gpl.html), or the Creative Commons Attribution License, version 2.5 (https://creativecommons.org/licenses/by/2.5/) or later.

All trademarks within this guide belong to their legitimate owners.

Authors

Andrew 'Drew' Jensen († Oct 4, 2021)

Publication date and software version

Published 04/10/07. Based on OpenOffice.org 2.0.4 or greater.

Introduction

Why?

The genesis of this particular tutorial database came from an email at users@dba.openoffice.org.

I am looking for an alternative to Lotus Approach which is a database in their own language. Looks and works like a spreadsheet and does a lot more beside which is important. I am using it for a membership database with around 36 columns and over 50 lines.

Each member's details is in a line. I need to access records quite frequently, weekly and monthly at least to change anything and everything:, e.g.; phone numbers; e-mail addresses, delete members; add new members; change addresses; change subscription details which occupy 4 columns and to get outputs. My access is required to remove existing or make up completelynew lines but mostly it involves altering the content of 1 to 4 Fields only, and to get different outputs.The outputs used are in 9 types; 6 in different mini-spreadsheet layouts from 3 to 15 different columns and all the 50+ lines; 1 form that includes all the individual's details, i.e.,. every field in a line for 1 member at a time; 1 that is for addressing envelopes; and 1 that will print a 3x7 labelA4 sheet of address labels.With Lotus Approach if any single field or line is changed on the main worksheet or any of the 'outputs' the identical field is changed at the same time automatically on every other one of the 9 outputs without requiring any additional action from me. I need this from OOo.Unfortunately, and the reason for me looking to use OOo is that very few people have Lotus SW and I have to duplicate my efforts and find work-a-rounds whenever output is required for MS Word documents or sending in e-mail messages. Spreadsheets on their own involved a lot of extra work keeping them in parallel and up to date or copying and pasting to produce extracts. This is when errors creep in. That is why I moved to Approach. I have very little knowledge of SQL, or equal, working and do not know how to proceed in this case. With Approach it is not necessary. I imagine that MS Access would do all that I want with ease but at a price that for me to finance personally is not-on. If I can get OOo working in the way I need, like Approach, it will obviously be of interest to others. It may alreadydo it all but I struggle to find out how, hence my requests for help when the Help pages get me lost!

Am I asking too much from OOo at this stage in its development?

Well, I don't think so and what follows is a step by step example of how to build a database to do something similar as to what this person was describing.

If you perform the steps described here I believe this should take roughly 2 hours to complete.

Objectives

• Demonstrate the use of Table, Form and Report wizards.
• Demonstrate the use of the Query Designer – including use of simple formatting functions within the designer to generate result sets specifically for the report wizard.
• Demonstrate how to edit a report created with the report wizard to add some summary data.
• Show the use of queries to supply both multiple forms and reports
• Demonstrate some helpful tips and tricks for building more friendly data entry forms
• Show how to generate labels using Writer and Base together
• Show how to generate mail merged files using Writer and Base
• Show how to link data from Base into a Calc document for export, with automatic refresh

What

The example database described here will consist of:

• 1 Table
• 4 Forms
• 4 Reports
• 7 Queries
• 1 Address label document
• 1 Calc export document

Create the database file

• Name: Members23
• Type: HSQL
• Connection: embedded

Create table using the wizard

• Business
• Contacts
• Select All Fields
•  Next 
• – rename ContactID → MemberID
• – rename ContactTypeID → MemberTypeID
• – Change the Field Type of MemberTypeID to TEXT[VARCHAR_IGNORECASE]
• – Order the columns using the up / down buttons

- Add Fields

• JoinedDate ( Date )
• RenewalDate ( Date )
• LastPayment ( Date )
• AmountDues ( Decimal(10,2) )

•  Next 
• Select 'Use an existing field as a primary key'
• Select field MemberID and check Auto_Value

•  Next 
• Name the table Members and Select 'Create a form based on this table'

•  Finish 

• Select all the fields
• 
•  Next 
•  Next 
•  Next 
•  Next 
• Change the style to Light Gray and the Field Border to Flat

•  Next 
• Name the form Member_master_list

•  Finish 
• The Member_master_list form will be open and ready for input.

Close the form window. At this point you have the Base file Members.odb, a table named Members and a form Members_Master_List.

– This would be a good time to save your work.

Now we want to create some more forms and reports.

Three forms more

( for a total of four forms ( views ) of the one table in the database ):

• Members_dues form ( list members by name and allow editing of the financial information )
• Members_Contact_Information ( list members by name and allow editing of members contact information )
• Members_Family_Information ( list members by name and allow editing of the members extended information )
• Members_Master_List ( this was the form created for us from the table wizard, it has all fields in the table )

Four reports

• Master_Members_list
• Members_Dues
• Members_Contact_list
• Members_Contact_Extended_List

You may be tempted at this moment to use the form wizard to just create these forms, for each basing the form on the table Members. However, I feel it is better to create queries that select the different fields wanted on these forms, then base the actual form on this query. For one thing, a number of queries can be used to feed both forms, sub_forms and reports. If there needs to be a change made to how some of the information is displayed, then an update to the query will update all other forms and reports that use the query.

All together there will be seven queries

• Query_Dues
• Query_Contact_Information
• Query_Family_Information
• Query_Total_Dues
• Query_Members
• Query_Count_Members
• Query_Birthday_this_month

Using the query designer to generate these queries.

Query_Dues

Start the designer

Add the table Members

Add the fields

MemberID, LastName, FirstName, RenewalDate, LastPayment, AmountDues

In the Alias column, rename the fields Last Name, First Name, Renewal Date, Last Payment, Amount

Keeping the designer open – select Run Query

• In the results window select the COLUMN Amount by clicking the word Amount.
• Right click and select Column Format
• This brings up the Field Format dialog box
• Select Category Currency and an appropriate currency display type

•  OK 
• Save the query as Query_Dues and close the designer window.

Query_Contact_Information

• Start the designer – Add the table Members
• Add the fields MembersID, LastName, FirstName, Address, City, StateOrProvince, PostalCode, EmailAddress, PhoneNumber, MobileNumber, FaxNumber
• In the Alias column rename the fields Last Name, First Name, St., ZIP, Email, Phone, Cell, FAX
• Keeping the designer open – select Run Query

• Save the query as Query_Contact_Information

Query_Family_Information

• Start the designer

Add the table Members

Add the fields

MembersID, LastName, FirstName, MaritalStatus, SpouseName, ChildrenNames

• In the Alias column rename the fields Last Name, First Name, Marital Status, Spouse, Children

• Keeping the designer open – select Run Query
• Save the query as Query_Family_Information

Because these queries are based on the single table Members, and so long as we include the Primary Key field from the table, any form based on the query will be updateable.

Start the Create form Wizard

• Select only the fields Lastname, FirstName, LastPayment, AmountDues, RenewalDate

•  Next 

Author:Drew Jensen 17:47, 11 September 2007 (CEST)
Please do not change the logical content of this site without acknowledge of the author.

Link

• Link to download sample-file: will follow