Is it possible to have more than one relationship stemming from the same field in a table? At the moment I have my central database which contains all my information. This centres round a list of people. Ideally, I want to create a separate linked table for each person, so that their personal information links to the main table.

I have created a relationship to a table based on one of them, but any further links I try to create just put my new information into the same table!

Alternatively, is there a better way to link this information; perhaps with hyperlinks?

Thanks,
Rachel

Rachel

Yes, you can have more than one relationship per field.

It is important that you go through a process called NORMALISATION. This is covered in depth in our intermediate course. There is good background information in the help section of Access.

Basically you need to look at the data your database is going to contain, and see what kinds of relationships they should have. NORMALISATION is a guide to doing this so that the result will work in a relational database. It does take some time to understand, and can be a bit technical.

A good start is if you have all your data in one table, and then look at the fields that have duplication in it. Those fields that have duplication are ones that can be put into a separate table. Not all these fields need to be separated from the main table.

So an example could be Customers and Orders. You would not want to have all that detail for each customer for each order in one table, as you would be constantly duplicating the customer info every time they re-ordered.
So in this case, the fields relating to the order (like product, quantity, amount, shipping details, etc) would be in one table, and customer information would be in a separate table. The way this would be linked is through the CustomerID. Each Customer would have a unique code (primary key), which appears in the order table (foriegn key). The relationship is built on these fields. So linking CustomerID(primary key) in the Customer table, to the CustomerID (foreign key) in the Orders Table.

The way you would view the information would be through a query. Show both tables in the query, make sure that the relationship is in place, and then show the fields you want to see. A good way to see if you have got the relationship right, is to show both CustomerID fields from both tables in the query. This way you can see if the fields match. If they do match, then you have got it right. If not matching, then you need to find the error.

Let me know how you get on.

Richard