Microsoft Access 2007 tutorial:create table relationships

Create table relationships

VIII. Creating the table Relationships

VIII.1. What is relationship?

A Relationship happens between a table and a table through a primary key of a table with a foreign key of another table.

VIII.2. What is Foreign Key?

A Foreign Key is a primary key of a table that exists in another table.

VIII.3. Types of Relationship

In Microsoft Access 2007, there are three types of table relationships.

- A one- to-one relationship: One-to-one relationship is a relationship that each record in the first table can have only one matching to each record of the second table and vice versa. This relationship happens in case you define it. For instance, an employee can have only one legal wife. From this example, you can create two tables:

- TblEmployee(EmpID,EmpName, Sex, Address, Phone,DOB, SpouseID)
- TblSpouse(SpouseID, SpouseName, Sex, DOB, Phone)

The two tables relate to each other throup SpouseID fields. SpouseID in the TbleEmployee is a foreign key referencing to the SpouseID primary key in TblSpouse.
Note – EmpID and SpouseID represent primary keys of the tables.

Table relationships

Note: To create a one-to-one relationship for these two tables, you have set indexed on Foreign Key (Spouse) by selecting Yes (No Duplicates).

- A one-to-many relationship: In one-to-many relationship, one record in the first table can have many matching in the second table. For example, a publisher can publish many books and one book can only publish by a publisher. Through this example, you can create two tables:

-TblPublisher (PubID,PubName, Address)
-TblBook (BookID, PubID,Title, ISBN)

The two tables relate to each other through PubID fields. PubID in the TblBook is a foreign key referencing to the PubID primary key in TblPublisher.


- A many-to- many relationship: In a many-to-many relationship, each record in the first table can have many matching in the second table and each record in the second table can have many matching in the first table. For example, one student can study in one room or many rooms and one room can contain one student or many students. In this case, you can create two tables:

-TblStudent (StudentID, StudentName,Sex,DOB, Addr, Phone)
-TblRoom(RoomID, RoomName, Floor)

In a many-to-many relationship, the third table id created as central table to connect between two tables above. The third table calls tblRoomStudent that contains two Foreign Keys of those two tables as you see on figure below.

Many-to-Many relationship


seo comment


Hello Web Admin, I noticed that your On-Page SEO is is missing a few factors, for one you do not use all three H tags in your post, also I notice that you are not using bold or italics properly in your SEO optimization. On-Page SEO means more now than ever since the new Google update: Panda. No longer are backlinks and simply pinging or sending out a RSS feed the key to getting Google PageRank or Alexa Rankings, You now NEED On-Page SEO. So what is good On-Page SEO?First your keyword must appear in the title.Then it must appear in the URL.You have to optimize your keyword and make sure that it has a nice keyword density of 3-5% in your article with relevant LSI (Latent Semantic Indexing). Then you should spread all H1,H2,H3 tags in your article.Your Keyword should appear in your first paragraph and in the last sentence of the page. You should have relevant usage of Bold and italics of your keyword.There should be one internal link to a page on your blog and you should have one image with an alt tag that has your keyword....wait there's even more Now what if i told you there was a simple Wordpress plugin that does all the On-Page SEO, and automatically for you? That's right AUTOMATICALLY, just watch this 4minute video for more information at. <a href="">Seo Plugin</a>


This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.