CODEWITHSUNDEEP
databasems-accessrelationship
Stiliyan Vasilev
Stiliyan Vasilev

Reputation: 189

Relationships Between Tables in MS Access

I'm new in DataBases at all and have some difficulties with setting relationships between 3 tables in MS Access 2013.

The idea is that I have a table with accounts info, a table with calls related to this accounts and also one table with all the possible call responses. I tried different combinations between them but nothing works.

Upvotes: 0

Views: 2163

Answers (3)

Gord Thompson
Gord Thompson

Reputation: 123409

To actually define the Relationships in Access, open the Relationships window...

Relationships.png

... then follow the detailed instructions here:

How to define relationships between tables in an Access database

Upvotes: 0

Palec
Palec

Reputation: 13551

Using this table structure:

Accounts : AccountID(PK) | AccountName | Language | Country | Email 
Calls    : CallID(PK) | AccountID(FK) | ResponseID(FK) | Comment | Date
Responses: ResponseID(PK) | Response
  • Accounts.AccountID is referenced by Calls.AccountID. 1:n – many calls for one account possible, but each call concerns just one account.
  • Responses.ResponseID is referenced by Calls.ResponseID. 1:n – many calls can get the same response from the prepared set, but each call gets exactly one of them.

Upvotes: 0

Leon
Leon

Reputation: 877

When you have a table, it usually has a Primary Key field that is the main index of the table. In order for you to connect it with other tables, you usually do that by setting Foreign Key on the other table.

Let's say you have your Accounts table, and it has AccountID field as Primary Key. This field is unique (meaning no duplicate value for this field).

Now, you have the other table called Calls and you have a Foreign Key field called AccountID there, which points to the Accounts table.

Essentially you have Accounts with the following data:

AccountID| AccountName | Language | Country | Email 

  1      | FirstName   | EN       | US      | [email protected]
  2      | SecondName  | EN       | US      | [email protected]

Now you have the other table Calls with Many calls

CallID(PK) | AccountID(FK) | ResponseID(FK) | Comment   | Date

  1        | 1             | 1              | a comment | 26/10
  2        | 1             | 1              | a comment | 26/10
  3        | 2             | 3              | a comment | 26/10
  4        | 2             | 3              | a comment | 26/10

You can see the One to Many relationship: One accountID (in my example AccountID=1) to Many Calls (in my example 2 rows with AccountID=1 as foreign keys, rows 1 & 2) and AccountID=2 has also 2 rows of Calls (rows 3 and 4)

Same goes for the Responses table

Upvotes: 1

Related Questions