Difference between revisions of "A club membership database"

From Apache OpenOffice Wiki
Jump to: navigation, search
m
(Numerous spam links deleted, Was paintedFrogCeramics.com once a valid domain that lapsed and has become a spam site?)
(9 intermediate revisions by 3 users not shown)
Line 82: Line 82:
 
* '''– Change the Field Type of MemberTypeID to TEXT[VARCHAR_IGNORECASE]'''
 
* '''– Change the Field Type of MemberTypeID to TEXT[VARCHAR_IGNORECASE]'''
 
* – Order the columns using the up / down buttons
 
* – Order the columns using the up / down buttons
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_m5def7edc.png : Image 2]
+
* [[Image:Tutorial_html_m5def7edc.png|Image 2]]
  
 
=== - Add Fields ===
 
=== - Add Fields ===
Line 89: Line 89:
 
* LastPayment ( Date )
 
* LastPayment ( Date )
 
* AmountDues ( Decimal(10,2) )
 
* AmountDues ( Decimal(10,2) )
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_m303cad1f.png : Image 3]
+
* [[Image:Tutorial_html_m303cad1f.png|Image 3]]
  
 
*  
 
*  
Line 95: Line 95:
 
* Select Use Existing Field as Primary Key  
 
* Select Use Existing Field as Primary Key  
 
* Select MemberID and Check Auto_Value
 
* Select MemberID and Check Auto_Value
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_7b685c1d.png : Image 4]
+
* [[Image:Tutorial_html_7b685c1d.png|Image 4]]
 
<Next>
 
<Next>
 
* Name the table Members and Select 'Create Form Based on Table'
 
* Name the table Members and Select 'Create Form Based on Table'
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_1397791d.pmg : Image 5]
+
* [[Image:Tutorial_html_1397791d.png|Image 5]]
 
<Finish>
 
<Finish>
 
*  
 
*  
 
*  
 
*  
 
* Select all the fields
 
* Select all the fields
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_m5def7edc.png : Image 6]]
+
* SPAM LINK REMOVED
 
<Next>
 
<Next>
 
* <Next>
 
* <Next>
Line 109: Line 109:
 
* <Next>
 
* <Next>
 
* Change the style to Light Grey and the Field Border to Flat
 
* Change the style to Light Grey and the Field Border to Flat
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_4e0376c3.png : Image 7]
+
* SPAM LINK REMOVED
  
 
* <Next>
 
* <Next>
 
* Name the form Member_master_list
 
* Name the form Member_master_list
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_233c198d.png : Image 8]
+
* SPAM LINK REMOVED
 
<Finish>
 
<Finish>
 
* The Member_master_list form will be open and ready for input.
 
* The Member_master_list form will be open and ready for input.
 
*  
 
*  
 
*  
 
*  
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_m3d6b5b38.png : Image 9]
+
* SPAM LINK REMOVED
 
Close the form window.
 
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.
 
* 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.
Line 161: Line 161:
 
* This brings up the field Format dialog box
 
* This brings up the field Format dialog box
 
* Select Category Currency and an appropriate currency display type
 
* Select Category Currency and an appropriate currency display type
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_edb7e0d.png : Image 11]
+
* SPAM LINK REMOVED
 
<OK>
 
<OK>
 
*  
 
*  
Line 172: Line 172:
 
* In the Alias column rename the fields Last Name, First Name, St., ZIP, Email, Phone, Cell, FAX
 
* In the Alias column rename the fields Last Name, First Name, St., ZIP, Email, Phone, Cell, FAX
 
* Keeping the designer open – select Run Query
 
* Keeping the designer open – select Run Query
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_m30549f18.png : Image 12]]
+
* SPAM LINK REMOVED
 
Save the query as Query_Contact_Information
 
Save the query as Query_Contact_Information
 
*  
 
*  
Line 183: Line 183:
 
* In the Alias column rename the fields Last Name, First Name, Marital Status, Spouse, Children
 
* In the Alias column rename the fields Last Name, First Name, Marital Status, Spouse, Children
 
* Keeping the designer open – select Run Query
 
* Keeping the designer open – select Run Query
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_4f4dd4df.png : Image 13]
+
* SPAM LINK REMOVED
 
Save the query as Query_Family_Information
 
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.
 
* 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.
 
*  
 
*  
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_m444aebea.png : Image 14]
+
* SPAM LINK REMOVED
  
 
*
 
*
Line 195: Line 195:
 
*  
 
*  
 
* Select only the fields Lastname, FirstName, LastPayment, AmountDues, RenewalDate
 
* Select only the fields Lastname, FirstName, LastPayment, AmountDues, RenewalDate
* [http://www.paintedfrogceramics.com/OpenOffice/club_members/tutorial_html_3bfcfbc8.png Image 15]
+
* SPAM LINK REMOVED
 
<Next>
 
<Next>
 
*  
 
*  
Line 201: Line 201:
 
*  
 
*  
  
[[Category:Database]]
+
''Author: [mailto:atjensen@openoffice.org?subject:OOoWiki Examples] [[User:DrewJensen|Drew Jensen]] 17:47, 11 September 2007 (CEST)''<br>
 +
''Please do not change the logical content of this site without
 +
acknowledge of the author.''
 +
 
 +
== Link ==
 +
* Link to download sample-file: will follow
 +
 
 
[[Category:Base Example]]
 
[[Category:Base Example]]
 
[[Category:Base Tutorials]]
 
[[Category:Base Tutorials]]

Revision as of 20:10, 3 April 2013

Base Tutorial

Building a club membership databaseChapter 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 (http://www.gnu.org/licenses/gpl.html), or the Creative Commons Attribution License, version 2.5 (http://creativecommons.org/licenses/by/2.5/) or later.

All trademarks within this guide belong to their legitimate owners.

Authors

Andrew 'Drew' Jensen

Feedback

Maintainer: Drew Jensen

Please direct any comments or suggestions about this document to:drewjensen.inbox@gmail.com

Acknowledgments

[Optional section.]

Publication date and software version

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

Contents

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
  • Image 2

- Add Fields

  • JoinedDate ( Date )
  • RenewalDate ( Date )
  • LastPayment ( Date )
  • AmountDues ( Decimal(10,2) )
  • Image 3
  • <Next>
  • Select Use Existing Field as Primary Key
  • Select MemberID and Check Auto_Value
  • Image 4

<Next>

  • Name the table Members and Select 'Create Form Based on Table'
  • Image 5

<Finish>

  • Select all the fields
  • SPAM LINK REMOVED

<Next>

  • <Next>
  • <Next>
  • <Next>
  • Change the style to Light Grey and the Field Border to Flat
  • SPAM LINK REMOVED
  • <Next>
  • Name the form Member_master_list
  • SPAM LINK REMOVED

<Finish>

  • The Member_master_list form will be open and ready for input.
  • SPAM LINK REMOVED

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 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 MembersID, 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 work Amount.
  • Right click and select Column Format
  • This brings up the field Format dialog box
  • Select Category Currency and an appropriate currency display type
  • SPAM LINK REMOVED

<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
  • SPAM LINK REMOVED

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
  • SPAM LINK REMOVED

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.
  • SPAM LINK REMOVED

Start the create form Wizard

  • Select only the fields Lastname, FirstName, LastPayment, AmountDues, RenewalDate
  • SPAM LINK REMOVED

<Next>

Author: Examples 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
Personal tools