Introduction to Databases

1. Introduction to Databases as a Health Promotion Tool

2. The infamous computer geek

3. The anti-geek

4. Objectives of this Workshop • To know when and why to choose database software over spreadsheet or word processing software for a project • To learn how to conceptualize a database that will fit your needs and be expandable • To learn how to create a simple database using Wizard • To learn how to modify a simple database to fit your needs • To learn how to develop queries and reports from a database

5. A Few Terms A database is a collection of related information. An object is a fundamental element of a database such as a table, query, form, report or macro. A table is a grouping of related data organized in fields (columns) and records (rows) on a datasheet. A field is a column on a datasheet and defines a data type for a set of values in a table. A record is a row on a datasheet and is a set of values defined by fields.

6. Access - Database Data entry is faster and easier Information retrieval is faster and easier Information can be viewed and sorted in multiple ways Information can be shared with others Duplicate entry is minimized Information can be shared with other programs Excel - Spreadsheet Exceptional tool for numerical data Analysis of numerical data is faster and easier Information can be viewed using charts and graphs Forms can be customized Data can be linked Information can be shared with others Information can be shared with other programs The Pros of Excel and Access

7. Choosing a Database • Best suited for more complex projects • For data which you will use repeatedly • For data which you will need in a variety of formats

8. Contacts List – keep track of staff, partners, volunteers, program attendees, potential funding agencies, etc. Work flow – track in-kind contributions, committees and their members, projects & staff time per project, resources used, etc. Inventory – track resources created, companies that you used in their making, which projects they were used with, etc. Reporting – track the types of information in which you must report on and design reports & queries that give you the types of information you need Track workshop and presentation participants by workshop/presentation Track the contacts you make and types of contacts Ways You Can Use a Database in Health Promotion

9. Steps to Create & Using a Database • Plan • Create Tables • Set Relationships • Create forms for entering data • Enter data • Create queries • Create reports based on those queries

10. Plan • Define the purpose of your database • Determine the tables that you will need • Determine the fields that you will need for each table and what their characteristics should be • Identify unique field values • Set the relationships between tables

11. Contacts List – keep track of staff, partners, volunteers, program attendees, potential funding agencies, etc. Work flow – track in-kind contributions, committees and their members, projects & staff time per project, resources used, etc. Inventory – track resources created, companies that you used in their making, which projects they were used with, etc. Reporting – track the types of information in which you must report on and design reports & queries that give you the types of information you need Track workshop and presentations participants Track the contacts you make Ways You Can Use a Database in Health Promotion

12. Define the purpose of your database • What is the nature of the work you are doing? • What is it about the current work method that is not efficient? • What kinds of questions (queries) need answering? • What do you want to do with the information? • Look at the reporting needs of your organization to get clues for these questions

13. Ways to Create a Database • From scratch • Use ready–to–use sample databases • Use Database Wizard

14. Determine the Tables That You Will Need • Each table should contain information on only one subject. • Again look closely at the types of information you will be working with. • Once you have set up one or two main tables, other tables for new purposes and new types of information can be added as needed.

15. Determine the Fields That You Will Need • Each field will have a specific kind of information. • Separate out your fields as much as is reasonable. For instance, first name and last name should be in separate fields.

16. Identify Unique Field Values • The characteristics of the field would be determined by the type of data • Text • Numerical • Currency • date/time • yes/no • hyperlink

17. Set The Relationships Between Tables • Relationships between tables allow Access to connect information from separate tables. • Unique field values will allow Access to connect information from separate tables. • This is accomplished by assigning the same unique field to two or more tables with the database recognizing that all data that is related to this unique field value is also related. • This unique field value is often in the form of an ID # (which the database can automatically assign).

18. The Primary Key Each table should include a field or set of fields that uniquely identifies each record stored in the table. This information is called the primary key of the table. • Auto number - the simplest way to create a primary key • Single-field primary key (i.e., an employee ID #, Social Insurance, or parts number) • Multiple-field primary key – for situations in which you can't guarantee the uniqueness of any single field

19. Entering Data There are several ways to enter data into a database • Enter directly into a table while in datasheet view • Create a form to make data entry easier • Import data from other sources (I.e., Word, Excell, or an email program)

20. Exercise One • Create a database using Wizard • Purpose – to track volunteers, staff, & partners • What kind of information will you need • Mailing labels • Lists of committee members, their addresses, phone numbers & email addresses • Tables needed • Names and Addresses • Committee information • Type of contact (volunteer, staff, partner, advisory committee member, participant, etc.) • In-Kind contribution information • Workshops or program information