Overview

Standards

Getting Started

Other Resources

Getting Started

This section introduces basic Database skills. As you read, you will be invited to "Try Out" the skill introduced. Each time you see the Interactive Mouse Icon, we encourage you to participate in a hands-on activity. Return to your own database application to practice as needed.

Note: If you are on a Mac, use the Finder to switch back and forth between the window for this tutorial and the window for your application. The Finder is in the upper right hand corner of your screen, and, by holding the mouse button down on the Finder, you will see a list of all applications open on your Mac. If you are on a PC, "Alt+Tab" will automatically switch between your open windows.

1. Preparation

1. Understanding & preparing data.
2. Common database menu items.
3. Choosing a pre-designed database.
4. Preparing to design your own database.

2. Designing a Database

1. Create a new database.
2. Create fields.
3. View options.
4. Create a layout with graphics, lines, and text.

3. Entering Data:

1. Modifying, adding, & deleting fields.
2. Adding new records.
3. Finding an existing record.

4. Managing Data:

1. Sorting records.
2. Finding multiple records(queries).
3. Creating reports from data.

5. Integration:

1. Mail Merge

1. PREPARATION

1.1 Preparation: Understanding & preparing data.

Understanding data ~
The first step to organizing a body of any information is assessing what type of information you have. Whether you transfer your information into a database all at once, or one piece at a time, it is important to understand how your "information" becomes "data". For example, you might have a list of students in your class and a stack of field trip permission forms. The details of this information will become your data on a database. Try to foresee the use you might have for organizing this information in a database. Who has not turned in their form? What are the emergency contacts for each student? Who has turned in field trip dues?

Preparing data ~
When you have an understanding of your data and some possible uses of it, you should prepare by gathering it all together in one place. Make a list of the categories of information you have. For instance, a list of categories for the the field trip data might look like this:

Student Name
Parent/Guardian Name
Emergency Contact Information
Permission slip turned in or not
Field trip fees paid or not

Consider a body of information that you might like to better organize and get more use from. Perhaps you have a library of books you loan to students in your own classroom that you'd like to keep better track of and evaluate which books have been students' favorites. Choose some data to practice preparing. Create a list of the categories for this data.

Back to Top

1.2 Preparation: Common database menu items.

The menu items in database applications vary quite a bit, however the basic features are quite consistent. You will have to learn the language used in your database application. Notice the similarities in the menu items for FileMakerPro, AppleWorks, and Microsoft Access. The yellow and green ellipses note the items that have very similar functionality, just different names and varying depths of complexity.

Become familiar with the menu items in your application. Slowly drag your mouse over each shortcut to familiarize yourself with your shortcut icons. You may want to use your Balloon Help feature under the Help menu. Your menu items will be grayed out unless you open a new or existing database, so you may want to click OK through a few prompts just to get inside a database file. Then you will be able to browse your menu items.

Back to Top

1.3 Preparation: Choosing a pre-designed database.

Most database applications offer pre-designed databases for your use. At minimum, they might offer an address book or a contact list. If you are simply transferring your rolodex onto a database, you may want to use a pre-designed database that came with your software.

In FileMaker Pro, you have to find the Templates folder within the applications software folder after going to File>Open.

In AppleWorks, go to File>New. After choosing the assistant, you choose a template. It may not be clear which of the templates listed are actually databases. The address book assistant is a database. It looks like this:

Choose the Assistant and the Address List database.

You will be prompted through a series of dialog boxes beginning with this one.

In Microsoft Access, go to File>New and select the Database Wizard option. You will be guided through a series of dialog boxes that will help you choose a pre-designed database.

Find the option to work within a pre-designed database in your application. Go to either File>New or File>Open. Look for a template, assistant, or wizard, depending on your application. Work you way through dialog boxes as they appear. Be courageous, the worst that can happen is you create a file you have no use for. You can always delete it later. You may want to take some time in the pre-designed database that you open as a result of this process. Most of what you see in the pre-designed database was created through the basic design skills introduced in this tutorial.

Back to Top

1.4 Preparation: Preparing to design your own database.

Any time you take to prepare for designing your own database from scratch will be well worth the effort. As you will see, your application will immediately require you to input a lot of details about your information and how you would like to organize it. While it might even be a simple database, you need to have your information broken down into more than just the original categories discussed in 1.1 of this tutorial.

All databases are based on the fundamental structure of records and fields. To create database that will tell me about each student and their particular status with regards to the field trip, I will be creating a record for each student. Within each record, all the information will have to be broken down into separate fields. For each field, you will have to decide on a label for the field and what type of information will be entered. Here's a table that illustrates breaking down the categories of data into fields, the label for the field, and the type of data that will actually be entered.

After going through this step of preparation, you will be ready to create a database from scratch on any database application.

Become familiar with the menu items in your application. Slowly drag your mouse over each shortcut to familiarize yourself with your shortcut icons. You may want to use your Balloon Help feature under the Help menu.

Back to Top

2. DESIGNING A DATABASE

2.1 Designing a Database: Create a new database.

To create a new database from scratch, have a detailed list ready of the fields you will want to define. Know what you will want to title the database, and be prepared to begin entering some of your data so that you can see how effectively you designed your database. You can always edit it as necessary, however it is best to get it as functional as possible right in the beginning.

Go to File>New. In AppleWorks, the dialog box looks like this:

In FileMaker Pro, you will need to title your database right away before it will open. After going to File>New, this dialog box appears. Be sure to pick a logical destination to which you save your database.

In Microsoft Access, go to File>New.

Once you create a database in Microsoft Access, you still have to Create a Table. MS Access is a very powerful database application which allows you to essentially create many databases within one database file. Each of these are called Tables.

Begin creating a new database in your application. Most likely, you will immediately be prompted to define fields. This is covered in 2.2 of this tutorial. You may want to read through 2.2 before beginning the creation of your new database.

Back to Top

2.2 Designing a Database: Create fields.

To create fields, you will need to know three things before proceeding:

1. What fields would effectively break your information into small manageable pieces of data?
2. What will you name each field?
3. What type of data will be entered in each field?

Most helpful would be to make a list similar to that presented in 1.4 of this tutorial. Applications will often not let you proceed without designating at least one field. While you can always add or delete fields later, it is ideal to have as many fields set up initially as possible.

In AppleWorks and FileMaker Pro, a dialog box for defining fields appears immediately after creating a new database file. Notice the similarities between the two. Enter each field name one at a time, choose a type, then click create. Do not click Done until you've created all of your fields.

After creating a database file, you will Create a Table and define Field Properties. The idea is the basically the same in that you must assign a field name and a data type. You may choose to use the Table Wizard to guide you through the process of creating fields, as well.

Return to the new database file you started to create in 2.1 of this tutorial. You may also want to just start again and create another new database file. Have your list of field names and types accessible. Create each field one at a time, and then click Done. At this point, your database structure will be created. Your database will have no data yet.

Back to Top

2.3 Designing a Database: View options.

You can view your database in a few different ways. Your data can be viewed one record at a time or in a list view, which looks like a spreadsheet. Depending on the number of fields in your database, the width of your List view can get long and cumbersome. You'll find these to be the two most basic views.

Notice the different places to find viewing options in the menus.

Note a few more linguistics differences. MS Access calls the List View, Datasheet View. It also calls Layout View, Design View. The most important thing is for you to become familiar with the views in your own application. Important Note: Your menu items will change depending on the view that you are in.

In a particular view, you will be able to perform specific functions otherwise unavailable. Linguistics aside, here are the most common views described:

• view that enables you to enter data while looking at one record at a time
• view that enables you to look at several records at once in a list layout
• view that allows you to edit the design and layout of that you will view during data entry
• view that facilitates a search for particular records

In Browse view, an individual record will appear. For example, see the following AppleWorks record:

In List view, records will all appear in a table, very much like a spreadsheet. For example, see the following FileMaker Pro database in List View. Also note where in FileMaker Pro to find the option for List View. It does not appear in the menu items above.

Find the menu item or shortcut icons in your database software that will let you view your data in these various ways. Until you actually have data entered, you may not notice much of a difference. Once you've entered some data, you may want to practice changing your viewing options once again.

Back to Top

2.4 Designing a Database: Create a layout with graphics, lines, and text.

Database applications allow you to customize the way all of your fields appear in a record. We just finished discussing views in 2.3. In order to design a layout for your fields, you first need to go into the Layout or Design view of your application. Once there, you'll see graphics tools appear which will allow you to manipulate each field name and field and the surrounding space as you wish. You can insert graphics, add lines, and text once your tools appear.

Find the view in your application that will let you customize the layout of your records. Practice using the tools. Try changing font attributes, adding lines, and inserting graphics.

Back to Top

3. ENTERING DATA

3.1 Entering Data: Modifying, adding, & deleting fields.

As you enter data, you might decide that you would like to modify a field. Perhaps you would like to adjust the data type of a field from text to date or number. Perhaps you realize that you created a field that is really not necessary and you would like to delete it. Perhaps you would like to add a new field altogether. Find the feature which will allow you to define your fields. A dialog box listing all of the fields you originally created will appear. Modify as necessary.

In your application, find your feature that allows you to define your fields. Practice highlighting a field and modifying it. Try creating an additional field, then deleting it.

Back to Top

3.2 Entering Data: Adding new records.

Data entry is the process of inputting your information into each field provided within each record. You can either create a new record, or add information to an existing record. To add a new record, you will need to make sure you are in the browsing, form, or list view. If you are in Design/Layout or Find view, the options to add a new record may be grayed out. You can create a new record by going to the appropriate menu item or by using your application's shortcuts. Most often, after entering data into a new record, it is automatically saved. Notice that the TAB key will easily transfer you from one field to the next.

Shortcuts might look something like this:

Practice adding new records to your database. Make sure you are comfortable with the data entry process, using the TAB key to move through the fields as necessary.

Back to Top

3.3 Entering Data: Finding an existing record.

Finding an existing record in a database is a valuable database skill. You may simply want to view the data, or you may want to modify the data by entering updated information. At this point, we will discuss finding just one record. For instance, if you have a database of student records, you might like to look up one particular student for some reason.

Your application may have a shortcut like this FileMaker icon:

You can also use the menu items if you cannot find a shortcut icon.

After going to Find in AppleWorks and FileMaker Pro, what appears to be a blank new record shows up. This is not to be confused with a new record, in fact. It is actually a tool to help you in your search.

For instance, in AppleWorks Layout>Find, if you were looking for a Laura Hina in 7th grade, you would type her name and grade in this find view, then click Find on the left. Notice the find tools and shortcuts on the left that appear in the Find view.

All searching in Microsoft Access is done through queries. This is covered briefly in 4.2 of this tutorial, and more comprehensively in some of the tutorials on the Other Resources page in this website.

Practice finding a single record in your document. Notice if your application has a shortcut to the find feature. Exact spelling is critical during finds.

Back to Top

4. MANAGING DATA

4.1 Managing Data: Sorting records.

You may want to browse your records in a particular order. For instance, if you have a database that tracks the amount of books your students have read, you may want to sort your student records by the number of books read. Sorting is always done in ascending(1-10) or descending(10-1) order. You may want to see your sorted records in ascending so you can note those who may need help with reading the most.

To sort your records, first make sure you are in the view that allows you to browse your data. While sorting, you can be in either the list view or the form view. Find your sort feature.

In FileMaker Pro, the menu item and dialog box look like this:

In AppleWorks, the menu item and dialog box look like this:

In Access, you can sort your results after doing a query(see 4.2). The sorting dialog box might look something like this:

Find your sort feature. Practice selecting fields to sort by and playing with their order. Make sure you understand the difference between ascending and descending.

Back to Top

4.2 Managing Data: Finding multiple records(queries).

You may want to find a particular group of records. Another name for this searching process is a query. This means that you are going to ask the database application you are in to perform a search of all of your records based on certain values within fields. A query can be as simple as asking for all records that have particular contents in one field. A query can get very complex, as well. Examples follow.

A Simple Query
Perhaps you would like to pull up all of the students who have turned in their field trip permission slips so you can create a final list of who is going on the trip. The steps for this are as simple as those outlined in 3.3 of this tutorial. After going to find, put the appropriate check in the field that you created called "Permission Slip Turned In". Click Find, and all of the records that have a check in this field will be compiled for you. You can view them one at a time or in a list view.

A Mildly Complex Query
Perhaps you have a database of student records for an entire intermediate school student body. A more complex query would be to pull up all of the records for 7th grade, girls, who haven't yet turned twelve. Perhaps you want to let them know they are eligible to participate in a contest with an age limit. In order to do this you, need to work with three field values in the finding process: grade level, gender, and birthday.

The more complex the query the more likely it is you will have to use operators and create equations based on the value of a field. This process varies quite a bit.

In FileMaker Pro, you can use operators within the blanked out record that appears when you go to the Find view:

In AppleWorks, you use the Match Records feature. The menu item and dialog box look like this:

In Microsoft Access, there are a variety of options for the queries that you can perform. Initiate a New Query and work your way through one of the options. The basic idea remains the same; use operators and/or functions involving the relevant field values to express the types of records you are looking for.

Practice searching for multiple records in your database application. Do not get intimidated by the potential that your application has to perform complex queries. Use the operators that are most familiar to you as you begin to explore(e.g. =,>,<). Make sure you understand the difference between a simple search to find one record and a query that involves several fields.

Back to Top

4.3 Managing Data: Creating reports from data.

One of the rewards of having data entered into a well designed database is being able to quickly pull reports together that facilitate analysis of your information. All database applications will have a feature that will allow you to design the layout of a report, decide which records in the database will be included in the report, and save the report for future use. These three things occur in different order and through different terminology depending on the application you are using.

• In Microsoft Access, it is called a Form. You will Create a New Form.
• In FileMaker Pro & AppleWorks, it is called a Layout. You will Create a New Layout from within the Layout view.

The idea is to have your data appear so that it reports just the specific information you need. For example, from the student records database called 7th Grade Homeroom, perhaps you want to create a Master Field Trip List. You want to be able to print out a clean copy to be able to share with various people such as the bus driver, the principal, the hostess at the aquarium, etc. To illustrate the concept of creating a list type report, follow along in these steps in AppleWorks. Note: Though the steps and terminology vary, all applications provide similar options.

Step One: Make sure you are in the layout/design view.

Step Two: Select the feature that will allow you to create a new report.

Step Three: Select the type of report and designate a title.

Step Four: Select the fields and their order which you would like to include in the report.

Step Five: Design your report. Modify, add, or delete headers and footers. Insert graphics. Change font attributes. Notice the before and after in this simple AppleWorks example.

Before & After:

Step Six: Browse your report. At some point you will need to do a find or a query to only show those students who turned in their permission forms. Use the skills introduced in 4.2 of this tutorial for assistance.

Reports can be valuable just to view on the monitor screen or in a printed version. If you do plan to print reports, be sure an look closely at your report before printing through print preview. Always look critically at your data to make sure your query was successful for your report. Errors in data entry will result in reports with misinformation.

Create a practice report based on your database information. What types of reports might be useful to you over time? If you do not yet have data entered to see your actual report, you may not be ready to work with this feature. The more data you enter, and the more accurately it is entered, the more valuable reports become.

Back to Top

5. INTEGRATION

5.1 Integration: Mail Merge.

Mail merge is when you have a word processing document that you merge with a database. For example, you may have one letter to send home with students to all parents. You create a letter in a word processing program and you merge it with your student records database. With a few clicks of the mouse, your letters will print out personalized("Dear Mr. & Mrs....") and properly addressed.

The way it works is that a word processing software application with have a Mail Merge feature. Upon choosing this feature, you will be able to choose the database you want to merge with and the fields within the database that you wish to plug in to your document. You create this mail merge master document inserting fields in particular places.

For example, from the student records database, you would insert the appropriate field in the place of the parent names and their addresses within your generic letter to all parents.

The most important step in the process, after saving, is to go to Print Preview or a similar feature that will allow you to preview the way your data was transferred to your word processing document.

For example, notice the Mail Merge features of Microsoft Word and AppleWorks:

AppleWorks Word Processing document merging with an AppleWorks Database

Microsoft Word document merging with a FileMaker Pro Database

Open your word processing application and find your mail merge feature. Explore how to create a form letter and merge it with a database that has relevant information.

Back to Top