Dealing with Database - Defining Relationships


In last week's article, we saw the point of joining two database tables together. Hopefully your remember the little rule - but just in case - here it is again.

"Give me all the records on Table B that are equal to the current records key value on Table A"

This could result in a single record being returned or a number of records. This is important as it defines the type of relationship that exists between two tables. You can have a one-to-one or a one-to-many relationship. This means you expect to get only one record back or many records.

Creating Relationships

These instructions relate to Access 97. If you use a different database, look for a similar item on your system. (FoxPro for example allows you to define relationships within the table properties area)

Use the Tools menu "Relationships" option. A blank screen will appear and you will be invited to add tables to define the relationships. As we only have 2 tables, add each table so that a box containing the tables' details is shown.

As mentioned in last week's article, a line will appear between the 2 tables linking them by their primary keys - the ID number. We don't want this so delete the relationship by clicking on it and pressing the DEL button.

Adjust the boxes so that you can see all the fields listed and move one of the boxes a little away from the other. This isn't crucial from the databases point of view - it's just makes it easier for us to read.

Now click and drag one of the EMPLOYEE_NUMBER fields and drop it onto the other EMPLOYEE_NUMBER field in the other table. A box appears showing the two EMPLOYEE_NUMBER fields listed and at the bottom is box that says "Join Type". You will see this is marked "Indeterminate". This means that the database is not sure if this is a one to many or a one to one relationship.

WE know it's a one to one relationship - so why doesn't the computer? The reason for this is that we have not told the computer that the EMPLOYEE_NUMBER field is a unique key. Close the relationship box by clicking on the cross in the top right hand corner. Save changes when asked.

Now go to the Table tab of the main database window. Click on the FORENAME table then click the design button. You will then see a list of the fields in our table - all three of them. Place your mouse anywhere over the EMPLOYEE_NUMBER field and click.

The copyright of the article Dealing with Database - Defining Relationships in PC Support is owned by Chris Cruickshank. Permission to republish Dealing with Database - Defining Relationships in print or online must be granted by the author in writing.

Go To Page: 1 2 3

Articles in this Topic    Discussions in this Topic