It reminds us of an Excel spreadsheet for the information is organised into rows and columns. If you have very little information to store then it's reasonable to use this format, and indeed Excel can be used a simple database.

Storing data in a flat database like this (a single two-dimensional table) can be inefficient though, and this becomes a significant problem in large databases. Even the above table shows some inefficiency - can you spot it? The inefficiency comes from the fact that some of the information in the table is repeated - all the information about Mr. Smith appears three times, for example, since Mr. Smith has three accounts. Not only is it inefficient to store the same information three times but if Mr. Smith moves to a new address then it would be easy to update the address in only some of the records, leading to great confusion.

The solution is to move the repeated information into a separate table and then eliminate the repeated data, like this:

Accounts table:

Customer table:

We have a problem! Now, when we look at the Accounts table we have no way of knowing who each account belongs to! We need to link the two tables in some way. We do this by making sure that each of the tables to be linked contains a common field. For example, we could include the customer Name field in the Accounts table as well as the Customer table. We would still have a small problem to deal with: what if a new customer, also called Mr. Smith, were to open an account? In the Accounts table, we would not be able tell which Mr. Smith holds the 'Smith' accounts! The solution to THAT problem is to make sure that our customer table includes a filed which is guaranteed to contain unique values - such a field is called a primary key. Since we can't use the name as a primary key (for there might be duplicate names) we will give each customer a unique Customer ID and we will include the CustomerID field in each table, like this:

Accounts table:

Customer table:

Once the link has been established between the two tables via the common CustomerID field we can now know what name and address corresponds to each of the accounts, with a minimum of repeated information.

Databases like this that contain multiple tables between which relationships (connections) have have been established are called relational databases and they have largely replaced the flat databases that were popular forty years ago.

More about relational databases

In relational databases data is stored in tables made up of one or more columns (Access calls a column a field). The data stored in each column must be of a single data type such as Text, Number or Date. A row in the table is called a record.

If different tables can have the same column in common then this feature can be used to explicitly specify a relationship between two tables.

The most common relationship is a 'one-to-many' relationship or a Master/Detail relationship and that is the type of relationship that we saw in the banking tables. In a one-to-many relationship, a single master record (such as Customer 1003, Mr. Axe) can have many detail records (the two accounts) associated with it.

In a one-to-many relationship, it is possible for a Master record to exist without any Details (in our banking tables we could have a customer who has not yet opened any accounts). However, it is impossible to have a Detail record without a matching Master record (we cannot have an account that does not belong to a specific customer).

Each table also should have a special column called the Primary Key that is used to uniquely identify rows or records in the table. Values in a primary key column may never be duplicated. In the above tables, the CustomerID is the primary key for the Customer table while the AccountNumber is the primary key for the Accounts table.

In any database application, each of the tables requires a means to get data into them and retrieve the data at a later time. The usual way to get data into tables is to use data entry forms. The primary ways to get data back out of tables or to display data in tables are to use queries or reports. For this tutorial, we will create a data entry form for each table, a query for each table and a report for each table.