Whether you're new to working with Access 2010 or you've used the application for some time, you may wonder what the term "Relational Database" means. This article describes this term and illustrates how it is used in Access.

Access contains data arranged in tables, with Field names as table headings and each table row representing a unique record. If you've used Excel, the Access table looks very similar to an Excel spreadsheet with its columns and rows data arrangement. However Access has the special capability of being able to easily link tables together. You can link spreadsheet tables together in Excel as well using different formula such as lookups, but it can be unwieldy, particularly if the data lists change. In Access once tables are linked in relationships, it's easy to work with data in different tables no matter if the table data changes.

When Access tables are linked in relationships, you create a link between two tables at a time. You do this in the Relationships view in Access. The link is made between fields common to both tables. So for example suppose you have one table listing the staff in a shop, with each member of staff assigned a particular ID in their primary key field. The primary key means that there is a unique staff ID for each member of staff and there are no duplicates. You also have a second table listing all the sales made by the staff. In this second table one field is used to log the staff ID of the person who made the sale. So as each sale is logged, we type in the sales details and the salesperson's ID, so we know who made the sale.

Because the data in the staff table ID field, and the data in sales table Staff ID field refers to the same person, we can create a link between these two fields. So we select the Access Relationships view. In this view you add the two tables in question, for example, our Staff and Sales tables. Then in this view we drag the Staff ID from the Staff table to the Staff ID in the Sales table, and then release the mouse. We then tick the checkbox for "Referential Integrity" and click OK to finish. Access now draws a link between the two tables in this view. You can see a 1 symbol at one side of the link and an infinity symbol at the other end. This is called a one to many relationship.

In practical terms this means that one member of staff can make many sales. The referential integrity feature means that staff must be added to the Staff table before they can be added to the sales table. The view can then be saved and closed. So in this example we've created a link between a primary key, the Staff ID field in the Staff table, and a non primary key, the Staff ID field in the Sales table. This is the commonest type in relationship link between Access tables and results in a one to many relationship.

Once a relationship has been created between two tables, we can create queries, forms or reports which use this relationship. In Access the analysis is usually done by the queries. A query can choose data from both related tables, for example to list staff names against each sale made. The query can also answer questions such as "How many sales has Mary made?" or "What is the average sales value for Fred's sales?"

You can create other links between tables in the database. Suppose in our example we also have a salary table for all the staff. Because the data is confidential, it's in in a separate table. The primary key field in the Salary table is also StaffID and other fields are used for gross salary, tax and national insurance. So the Salary table lists all salary details for each member of staff and the StaffID data is the same as in the Staff table. So each person in the Staff table has a corresponding entry in the Salary table.

If we now select the Relationships view and add the Salary table to the view, in addition to the Staff and Sales table, we can link the Staff and Salary tables. To do this we drag Staff ID from the Staff table to the Staff ID in the Salary table and release the mouse. We then tick Referential Integrity and click OK to complete. Now you'll see these two tables linked in the view. Notice that this link shows with 1 symbols beside each link. Linking a primary key in one table to the primary key in another table results in a one to one relationship. And again, the referential integrity feature means that staff details must be added to the staff table before being added to the Salary table.

Because the tables are linked, we can create a query to show, for example, each person's name and their full salary details, perhaps use by the accounts department in preparing wage slips.

So an Access relational database consists of tables which have common fields linked in different ways. This links are created in the relationships view in one to many and one to one relationships. These relationships allow users to create queries which show data from different related tables. Forms and reports can then be created based on these queries to create, for example sales reports or wage slips which show data from multiple tables.

Hopefully this short article has given you a brief insight into Access as a relational database. If you'd like to learn more about Access consider attending a training course. There are lots to choose from and the best ones give you lots of hands on practice under expert guidance.