tables in access 2007

creating the database

Before we start creating tables, we have to define and store the actual database file.

Office Access 2007 has a whole series of database templates that you can use if your application fits. And even if the application you have in mind isn't 100% similar to the template, you can still use the template as a starting point and then modify the tables it contains to suit your needs.

For the purposes of these tutorials, although there are several business models that we could use, we'll create a new database for Mike's Garage and we'll call it MikeGarage.accdb.

Access2007 templates

defining tables

In a relational database, data is collected into tables (called relations in relational theory).

A table represents some class of objects that are important to an organization. For example, a company may have a database with a table for employees, another table for customers, and another for stores. Each table is built of columns and rows (called attributes and tuples in relational theory). Each column represents some attribute of the object represented by the table.

For example, an Employee table would typically have columns for attributes such as first name, last name, employee ID, department, pay grade, and job title. Each row represents an instance of the object represented by the table. For example, one row in the Employee table represents the employee who has employee ID 12345.

When organizing data into tables, you can usually find many different ways to define tables. Relational database theory defines a process called normalization, which ensures that the set of tables you define will organize your data effectively.

creating tables

In Access 2007 a table can be created from a template, built from scratch or a combination of both. For the first table, Customers, we'll use a table template. The Contacts template is close enough to our needs so we'll use that one.

Any fields that need to be changed can be accessed easily from the Design View.

The Parts table we create from scratch. It's simply a matter of describing the fields, the data types and the properties that we need.

With Access 2007 it is also possible to create a table at runtime. By entering data into a datasheet the fields will take the datatype of the data input.

To a database designer the whole process looks very messy and unorganized. In other words, I would never use it. I much prefer to plan everything ahead and create the tables with a definite structure.

But, if it suits your needs ...

table relationships

Use the Relationships diagram at the end of the previous lesson as a guide to what you should get for Mike's Garage.

Open the Relationships window from the Database Tools tab.

Access2007 relationships

Tip 1: Make sure that the fields you are using in a relationship are the same type: Text -- Text, Number -- Number. Note that AutoNumber is not actually a data type - the data type is Number with an Automatic increment function. So, an AutoNumber field links to a Number field. There can only be one AutoNumber field in a relationship.

Tip 2: Do the relationships on empty tables, right after you create them. If you try to apply referential integrity to a relationship and the tables are corrupted, you'll get an error message. Then, you should delete all test data from the tables before you go on.

Drag the cursor between the two matching fields in the tables.

It is highly recommended to Enforce referential integrity. This means that there must be valid data in the two tables. In our example, the supplier ID must be valid when we put it into the Parts table. In other words, the database won't let me assign a non-existant supplier for a part. And if I ever change a supplier ID, Cascade update means that the new supplier ID will be assigned to all the parts where it's used.

Access2007 relationships