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.
 

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

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.

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.

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:

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.

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

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.