|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.
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:
Find Duplicates Query
Find Unmatched 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.
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.
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
|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
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.
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:
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:
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
And if we were to ask the question about animals with no claws:
We get this result.
Here is the answer---
Go to to
earn points for this topic.
Back to home page
Back to table of contents