an Access Database:
A Primer for Resource Center Managers
by
Laura C. Larsson
Examining an Existing Access 97 Database
Table of Content
IntroductionOpening a Sample Access Database
Opening a Previously Opened Database
Looking at the Structure of a Database
Looking at the structure of a table
Looking at tables and relationships
Entering data
Looking at the data
Forms
Querying the database
Printing the contents of a table
Reports
For More Information
Introduction
I've found it helps to open up someone else's database and take a look at how it's constructed before you begin planning your own database. People who know how to design databases are infinitely creative. Because of their creativity and design, you can get and adapt their methods for your own databases.Access comes with a sample database for you to use right away or adapt for your own purposes. This is the NorthWind database. We will not be examining this database.
You can also download several databases from the Microsoft Website.
Databases that I've downloaded onto my desktop at home include, Music Collection Manager and Wine List Manager. I don't use either one, but I like to look at how the programmers have organized information in the database. Other, more useful databases for downloading include the Book Collection database, the Event Management database, the NorthWind database, and Students and Classes database.
If you are taking this module as a workshop you will be provided with the Students and Classes database. Look in Sample databases (C:\Msoffice\Access\Samples) for the database called Students and Classes1.mdb.
If you are taking the workshop on the web, why don't you go to the Microsoft Website and download the Students and Classes database.
Click on the database and download it into your downloads subdirectory into a sudirectory of its own. I like to keep the downloaded files separate from the executed files so that I can keep straight which is which. The downloads subdirectory also acts as a backup copy in the event that I trash a database in my playing with it.
After you've downloaded it, you might want to spend some time looking at it. In fact, that's just what we're going to do now.
Opening a Sample Access Database
From the File menu, select Open Database. Change to the MSOffice subdirectory and then to Access. There you will see two additional sudirectories, Ready-to-Run and Samples (see image).
Go into Samples and open Students and Classes1.mdb.
Opening a Previously Opened Database
An Access database has the extension .mdb You open it just as you would any other Windows file by selecting File | Open and then giving the name of the database or finding where you've stored it on your hard drive.Looking at the structure of the database
When you open a database Access will display all of the database objects in the Database window.Database objects include tables, queries, forms, reports macros, and
modules. In general, in Access, the term object is used to refer
to something you can select and manipulate as a unit.
| Object | Definition |
| Table | A collection of data about a particular subject. The data in a table is presented in columns (called fields or attributes) and rows (called records). An Access table looks very similar to an Excel spreadsheet. |
| Query | A question you ask about the data in the database. Queries help you find information you want to use from the tables. The result of a query is a set of records called a dynaset. When you make a query of the database you are weeding out unnecessary information. |
| Form | A customizable way to present data on the screen. Using forms is often the most convenient way to enter, update and view records in the database. Most forms look like a fill-in-the-blank sheets you use when you fill out a job application. Forms and tables are linked. Information you fill out in a form is entered into the correct table. You can enter data into multiple tables from a form. |
| Report | A customizable way to present data on the printed page - or on the screen. Reports summarize and organize the data in the tables. |
| Macro | A list of actions which Access can carry out automatically. |
| Module | Access provide a programming language, Access Basic. Access basic is used for creating more complex procedures than macros can provide; these procedures are stored in modules. |
This is the structure of the Students and Classes database.
Looking at the structure of a table
To look at the structure of a specific table, click on the name of table you want to examine and then click Design. You will see the database design window for that table, which provides information on each attribute, including its name, format, length, description and any special rules like restrictions on entry.Click on the table name: Students, then on Open, or double click the table name to open the table.
| Practice:
Look carefully at the structure of the table Students. What do we see when we look at each of the fields? What does this table tell us about students? What is the subject of this table? |
Looking at tables and relationships
To look at a diagram of how the tables in a database are related, you can view the Relationships window. Choose Edit | Relationships or click the Relationships button. The diagram shows you the tables in the database and the connections between them. The “1” indicates the one side of a relationship, and the “infinity“ symbol indicates the “many” side of a relationship.If there's a way to print an entity relationship diagram using Access, I have not been able to find it. Microsoft has a fix (add on to your program) you can download from their Website which will enable you to print the entity relationship diagram.
Full sized version of this diagram.
| Practice:
Look at the database entity relationship diagram for the sample database. What can we learn about the relationships between the tables? |
Entering Data
Let's add some data to the Student table. Enter the following 2 records using the Datasheet view.| Practice: | Enter This Data | Enter This Data |
| Column Heading | Record 1 | Record 2 |
| Student ID | automatically generated for you - 1 | automatically generated for you - 2 |
| First Name | Margery | Murray |
| Last Name | Bird | George |
| Address | 12203 West Douglas Blvd | 905 First Avenue |
| City | Seattle | Philadelphia |
| State/Provence | WA | PA |
| Postal Code | 98003 | 10045 |
| Phone Number | (425) 890-4567 | (433) 988-2234 |
| Major | Hand spinning | Basket weaving |
| Student# | 456789 | 974555 |
Looking at the data
To see the contents of a table, either click on the table’s name and then click Open, or double click on the table’s name. Scroll though the data by using the scroll bars or by using the Page Up and Page Down keys and the arrow keys.| Practice:
Open and browse another table in the sample database. Close it and open/browse the other tables. Are you starting to see any patterns to the way the tables are set up? |
Forms
We will describe in detail later how to create your own forms, but looking at those in the sample database is instructive. 
| Practice:
Click on the Form tab. Click on the Students form and click Open. What information is collected on the student in this form that is not collected in the Student table? Be aware that the database can contain, and the form can display, pictures and other graphic objects, although it doesn't happen to show in this particular form. |
Querying the database
We will discuss queries in great detail later. However, several queries have been programmed for the sample database, and looking at them will give you an idea of how queries are used.
| Practice:
Choose the Query tab. Click on one of the defined queries and then click Open. Notice how each query presents data in a different way than the same data appears in the table in which it is stored. The records or columns may be in a different order, or only certain rows or columns may be displayed. In the case of the Cumulative Class Percentiles Information query, the query brings together data from three separate tables, the Assignments table, the Results table and the Students and Classes table. |
Printing the contents of a table
You can print contents of a table if the table is open or if you select it in the Database window. Choose File | Print or click the Print button on the toolbar. Note: Do not print anything. Abort the print by replying 'no' to the request to continue.| Practice:
Practice looking at, but not printing, the Switchboard table in the Students and Classes database. What is the Switchboard table used for? |
Reports
Reports allow you to get information out of the database in a format you've created.Reports can also be printed.
| Practice:
Click on the Report tab. Click on the Student report and click Preview. This report would produce a nice list of the students, in alphabetic order by the student’s last name. Note: We would need to add data to make the other reports work. |