Creating a database

in MS Access 97

Quick guide

Introduction: This guide shows phases of implementing a sample database for small company in Microsoft Access 97.

Step 1 Creating tables

a) Table Customers and table Products

The figure below shows the project window of table Customers. You can see names and types of fields. Read the descriptions to get additional information.

And here you have the Products table. Note the use of currency type.

b) Tables: Orders and OrderDetails

At this picture you can see project windows of two tables - Orders and OrderDetails, and the Indexes window of the table OrderDetails. Note that each foreign key besides ColorID has an index. Because there is not many records in Colors table, adding an index for that field is not required.

Step 2 Defining relations

Objaśnienie prostokątne z zaokrąglonymi narożnikami: Fields marked by bold font are primary keys.

This is one of the most important steps; without relations your database would be useless. The figure shows window with already defined relations and dialog box with properties of one.
Note that every relation is attached at one side to primary key of table. This assures that there will be only one record connected to every record in the second table. Table OrderDetails is in many-to-one relation with three other tables: Orders, Products and Colors. But it can be also interpreted that Orders is in many-to-many relation with table Colors and Products

Step 3

Entering data

After defining the tables, you can enter basic data to them, for example customers or colors. Entering them isn't very complicated, so it can be simply done by opening table (without creating a form).

Step 4 Adding forms

Because the process of creating forms is too complicated to show it on few pictures, I can only present the final look at form Orders:

Step 5 Adding queries

This is simple query, which was used as a base for following report.

Step 6 Adding reports

I will present only one basic report, which shows customers and their orders. The last page is a printed MS Access 97 report Customer Order Details.

Step 7 Using the database

While using databases you always have to be prepared that something won't work as you imagine. It's not your fault. It just has to be like that. I can give you only advice: never give up!

I wish you the best experiences with databases

Marcin Wudarczyk

[email protected]