DATABASE


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

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.

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.
 

What Are the Parts of a Database Program?

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.

Learn to move, insert, delete, rename fields

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.

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 clickOK.

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.

Activity for Database

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!!!!!

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 databaseFIELDS
___________________________________________________
Name                 Habitat        Food    Claw  Feet  Armor

How to do it---

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.

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


 
  
Dinosaur Habitat Food Claws Feet Armor
Iguanodon Land Plants No 2 No
Proceratops Land Plants No 4 Yes
Trachodon Water Plants No 2 No
Ankyosaurus Land Plants No 4 Yes
Allosaurus Land Meat Yes 2 No
Brontosaurus Water Plants Yes 4 No
Coelophysis Land Meat Yes 2 No
Brachiosaurus Water Plants No 4 No
Corythosaurus Water Plants No 2 No
Diplodocus Water Plants No 4 No
Triceratops Land Plants No 4 Yes
Gorgosaurus Land Meat Yes 2 No
Stegosaurus Land Plants Yes 4 Yes
Tyrannosaurus Land Meat Yes 2 No
Now your database is complete.


 
 

Sorting the DatabaseTo begin a sort, under "Records" select "Sort"and you will get two options, A-Z, Z-A. This will sort your data both alphabetically and numerically, ascending or descending

Filtering the Database

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:

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:


 
  
Dinosaur Habitat Food Claws Feet Armor
Iguanodon Land Plants No 2 No
Proceratops Land Plants No 4 Yes
Ankyosaurus Land Plants No 4 Yes
Allosaurus Land Meat Yes 2 No
Coelophysis Land Meat Yes 2 No
Triceratops Land Plants No 4 Yes
Gorgosaurus Land Meat Yes 2 No
Stegosaurus Land Plants Yes 4 Yes
Tyrannosaurus Land Meat Yes 2 No
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:


  
Dinosaur Habitat Food Claws Feet Armor
Trachodon Water Plants No 2 No
Brontosaurus Water Plants Yes 4 No
Brachiosaurus Water Plants No 4 No
Corythosaurus Water Plants No 2 No
Diplodocus Water Plants No 4 No
Upon examination, we see that dinosaurs who lived in water were vegetarians and had no armor.

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


  
Dinosaur Habitat Food Claws Feet Armor
Proceratops Land Plants No 4 Yes
Ankyosaurus Land Plants No 4 Yes
Triceratops Land Plants No 4 Yes
Stegosaurus Land Plants Yes 4 Yes
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.


  
Dinosaur Habitat Food Claws Feet Armor
Iguanodon Land Plants No 2 No
Proceratops Land Plants No 4 Yes
Trachodon Water Plants No 2 No
Ankyosaurus Land Plants No 4 Yes
Brachiosaurus Water Plants No 4 No
Corythosaurus Water Plants No 2 No
Diplodocus Water Plants No 4 No
Triceratops Land Plants No 4 Yes
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

Products:  Go to database products to earn points for this topic.


Back to home page
Back to table of contents