What is a Database?	Sorting the Database
How to do it---	Filtering the Database
Activity for Database

Please read all the way to the bottom of the tutorial for instructions on what to do next.

Some tutorial sections have video example links. Click on the link & a video demonstration will pop-up.

You must have RealPlayer 7.0 or greater to play the clips.

If you don't have RealPlayer, go here to download it for free.

This tutorial covers the basics of using Microsoft Access 2000, a database program. There have been few changes between Office 97 and Microsoft Office 2000, but remember that Office 97 cannot open an Office 2000 document in Access.

Introduction Video

What is a Database?

A database is an organized collection of data. Database programs enable you to put a collection of information into a specific order: alphabetical, numerical, chronological, or some combination of the three. There are two kinds of databases: flat file databases and relational database.

After you have a database with one or more tables, you can search for information with queries (questions) and can generate reports, all of which can be saved separately.
 

In Access, a database is comprised of one or more tables. A table has information stored in records.  Records are comprised of fields.
 

You may think of a database as an electronic box of file cards.  You could have cards for each class in the day, grouped together. Each group of cards can be thought of as a table.  Each card is a record, and the information on each card are fields, such as name, address, phone, zip code, and so forth. In a flat file database, all information is stored in a single database file or table. A relational database stores information in related database files or tables. Database Analogy Video

In Access, there are four main object types: tables, queries, forms, and reports.

What is a Table?

Each table contains information about one subject, such as class enrollment. One table might store student names and addresses while another stores test scores. Tables are made up of records, which contain all of the information about a single item, such as a student. Each record is broken into field. If you are working with a fully relational database that stores and retrieves information in more than one table, a form is better and more efficient because it links information stored in more than one table.

What is a Query?

A query is a question. Ask the queries you want and you can obtain selected data from one or more tables. The only parts of a query that Access stores are its design parameters. You can request data from several different sources for unique problems. There are a number of kinds of queries, such as simple ones that ask for names, or zip codes. In research, the crosstab query is used frequently, so you may want to experiment with this one more than others. For example, you may want to select on the basis of a variable (i.e., gender, ethnic classification, SES) to some other variable, such as achievement scores.

As in other cases, the Wizards simplifies queries, although it is limited, but for certain specific research questions this may be all you need to use. The major queries in Wizard are:

Crosstab Query
Find Duplicates Query
Find Unmatched Query
Archive Query

To Create a Query Using Wizards:

1. In the Database window, choose Query button.
2. Choose New.
3. Choose Query Wizards button.
4. Follow the directions onscreen for the query you need.

Otherwise, you can design your own queries.

1. In the Database window, choose Query button.
2. Choose New.
3. Choose New Query button and a Select Query window opens.
4. Click the table containing the data you want, then choose the Add button. You can also double-click on the table. To
    select multiple tables, hold the <Ctrl> key and click on each table you want. Next choose the Add button and there
    will be a field list for each table you select in the query window.

5. Choose the Close button.

The Query Design Window

Matching Fields and Linking Tables

Access automatically creates joins if two tables in your query have the same field name and data type. Or you can create these joins yourself. Lines linking the tables from the matching fields will appear in your Query Design Window. Access uses the symbol "1" for the primary or source table and the "[[infinity]]" symbol for the secondary side. This "one" to "many" relationship can be established between as few as two tables and as many as you want.

Move, Insert, Delete, Rename Fields

Access allows you to choose which fields you want to include and use in your query, and also to search for specific data. To begin a query, you must choose which table or groups of tables you want to search in. You can also use previously created queries for more specific searches. After selecting the tables(s) for your query, you can then add any of the fields, in any order, from your table by using the pull down boxes in the lower half of the query design window. Use the sort and criteria cell to enter search expressions. When you have all of the fields and expressions entered, choose "Run" from the Query menu to see the results of the query. The types and uses of expressions are explained later in the tutorial.

What Are Forms?

In forms you can enter data into your database, display this data for review, and print it out. In Access you use a form to enter and edit data because you can enter information to be stored in more than one table on a single form. You can enter data using tables, but if you use Access often this can become a problem because of the total amount of data that may be repeated. If you find it easier to use tables for your dissertation or other project, then go ahead and do it.

Using forms is potentially difficult and complex, especially if you operate in Design View. For most purposes, it is best to use Wizards:

AutoForm: This form automatically displays every field in the underlying table. It is also accessible from the AutoForm button on the toolbar.

Single-Column: This form displays the values in one column with each field on a separate line.

Tabular: This form displays several records at the same time in rows and columns.

Main/Subform: A Main/Subform form shows a one-to-many relationship between the data in the main form and the data in the subform. The main form is in a single-column format, and the subform is in a datasheet format

Graph: This form displays the selected data as a graph by using Microsoft Graph

If you choose not to use the FormWizards, then you will start with a blank form. You then need to place all of the desired fields on the form and decide what kinds of controls you need.

What is a Report?

A Report is a printout of specific information you request. Access has a Report Wizards.

To use the Report Wizard, choose "Create report by using wizard." Next choose which tables or queries you want to include in your report and choose the fields you want to display from the list of available fields. You can rearrange the order of the fields using the grouping levels, or go to the next screen. The next screen in the Wizard gives you the oportunity to sort your data if you have not already done so. The wizard will next ask a few questions about the look of the report. You can choose the look you want. Give the report a title, & click finish. You should see the report on the screen. Use Design View to add a label to the header or footer, or make changes to the appearance of your report.

Entering Data into an Access Database

There are two main ways to enter data into an Access database: tables or a form. In a table, the field name is at the top. On a form, the field name is at the of the field. When you have finished an entry, move to the next field by using <Tab>, the left arrow key, or your mouse.

Saving the New Record

Before you can exit, all fields must be used.

Printing from Your Database

You can print from any point in Access by using the Print button on the toolbar or Print from the File menu. When you print from a table, query, or form, you will print what appears on your screen.

Entering Expressions

Access gives you a great deal of flexibility in how you can enter expressions. To specify criteria as either a simple word, like Castro, or as a complex definition, "Between 500 And 1500," you can type the expression in the queries and filters.

Access uses Boolean operators to carry out its queries. You will usually base every select query on these And, Or, and Not operators. You may also use wildcards.

You can identify a range of records in an expression by using the Between . . . And operator or the comparison operators (<,>,<>,<=, and >=). The following examples show various operators.

EXPRESSION MEANING

>123 Numbers greater than 123

<123 Numbers less than 123

>="Castro" All text from Castro to the end of the alphabet

<="Castro" All text from the beginning of the alphabet to Castro

Between 10 And 20 All numbers from 10 to 20, inclusive

<>"Castro" All text before and after Castro

Like "Cas*" All text beginning with Cas

How to do it---

Step 1: Click on the Access icon

Step 2:    Click on "File" and then on "Blank Database" and then click OK.

Step 3: You must now create your access file and name it. When you do this it must also be saved. Make sure you save it to your disk in the A drive (unless you are working on your own computer). Give the file a name, and then click on Create. (The database will have a name and the table you create will have a different name.  Remember, the database is like a box and can hold several different tables.)

Step 4: At this screen you will select table to create your new table, and then click on New.

Step 5: You will now create your form to enter in the data. Choose datasheet view, then click OK.

Step 6: Your screen should look like this.

Now you may begin to enter your data and create your fields. Title field names by clicking in the gray areas field1, field2, etc... once you click you may type in a new field name. Then simply click in the square below the field name to enter the data.

The following is a dinosaur database. If you follow these steps, you will learn how to setup a database in Access, how to name fields, and how to query the database. This is a database that has been used for years in a number of applications.

Be sure to do the exercise or you won't know how to do the databases.  Do this one and the rest are easy!!!!!

Database Activity Introduction Video

The dinosaurs have names, ate certain kinds of food (meat or plants), lived on land and/or in water, either had claws or not, had two or four feet, and had armor or not. These categories---or fields---are our main interest. The first thing we will be concerned with are the "Fields" of the database. In this case, the fields are:

Dinosaur, Food, Habitat, Claw, Feet, and Armor.

Data for Dinosaurs database FIELDS
___________________________________________________
Name                 Habitat        Food    Claw  Feet  Armor

How to do it---

Database Activity Part 1 Video

Step 1: Start Access, create a NEW Database, New TABLE and begin in a New Datasheet View. Next name your field names and enter the data, it should look like this.

Database Activity Part 2 Video

Step 2: The next step is to make sure each field is formatted correctly, text is text and numbers are formatted for numbers. You can also format for time, dates and currency. To do this, choose VIEW from your menu and click on "Design View" Your screen should look like this.

At this screen you can specify formatting for your fields. For example, here is what the screen will look like to format the field "feet" for numbers.

Step 3: Complete the table, as follows: When you are finished, choose View again and click on "Datasheet View" to return to your table.

Step 4: Continue until all your data has been entered into your fields. When you are finished your database table should look like this
 
 

Now your database is complete.

A filter, sometimes called a query, is a method of selecting information in your database.

Step 1: To begin a filter, click under "Records" select "Filter, then Filter by Form"
and get this:

Database Filtering Part 2 Video

We are going to ask questions of the database. The first one is:

"What did dinosaurs who lived on land have in common?"

Step 2: To ask the question, we want to search the habitat field for dinosaurs who lived on land. To do this we enter the following in the space provided:

We can select "Habitat" by clicking. We type in the criterion, "land"-- Then we choose "apply filter/sort" from the Records menu, or you may click on this icon to apply the query:  and we get this result:
 
 

By examining the results, we see that there were no real commonalties for these animals.

Step 3: Before entering our next question, we go to the Records menu and select "Remove Filter/Sort" so we can get all of them back. Now we are ready to enter another query.

Step 4: Select "Records" and "Filter and Filter by Form" again. We are going to ask, "What did dinosaurs who lived in water have in common?" We use a similar procedure as before, except that this time we put in "Water" instead of "Land" in the criteria cell. By applying the query  we get the following:
 

Upon examination, we see that dinosaurs who lived in water were vegetarians and had no armor.

Database Filtering Part 3 Video

Step 5: Next, we may be interested in seeing what animals with armor had in common, if anything. We select "Records" and "Filter and Filter by Form" and then enter a new question, as follows: Armor , and the answer is yes, will get these results
 

We can see that these animals were vegetarians and were four-legged creatures who lived on land.

And if we were to ask the question about animals with no claws:

We get this result.
 

Suppose a scientist found some "bones" in an area that was once an ocean bottom. He announces that this was a dinosaur, it had claws, and it lived in water. What could this animal be? What did it eat? Did it have armor? Using our database, we put in this query, using two questions:

Here is the answer---
 

ID	Dinosaur	Habitat	Food	Claws	Feet	Armor
6	Brontosaurus	Water	Plants	Yes	4	No

Next Step :  Go to the streaming video/database measures to finish the tutorial.