# Database Discussions > Microsoft Access >  Need to create foreign key in MS Access

## dvorasnell

Plesae help! I am building a relational database with 2 main tables that should relate in a one to many relationship. I have gone to the design tab created relationship, done the join but cannot enforce referential integrity.

The database tracks managers and their accounts. ONe table tracks managers and the subtable (the many) tracks their accounts. 

Of course there are managers without accounts as yet, but Access will not let me enforce ref integrity saying that managers may have records not found in accounts. Of course that's true. 

Can you please help me set this straight. 

And ultimately, I want to ensure that if I have to delete a manager, I can delete all associated accounts at the same time.

I think Foreign Key may be the answer, but I have no idea how to do so.  

Thank you

----------


## Allan Murphy

An example

Create a table called tbl_managers with the following fields
manager_ref, manager_name  etc. Make manager_ref the primary key.

Create another table called tbl_accounts with the following fields account_ref, manager_ref etc make account_ref the primary key. Make sure the manager_ref in both tables have the same data types e.g. both are text or both are bnumber etc.

Using the relationship screen add the two tables to the screen now create a join from the field manager_ref in tbl_managers to the field manager_ref in tbl_accounts. When you finish the join a popup will be displayed then you can finish the join criteria  there should be 1 next to the tbl_manager and the infinity next to the tbl_accounts this means that one manager can have many accounts.
Hope this useful, for a better example look at Northwind.mdb sample database

----------


## aliciaishot

I am creating a database, I'd like one to many...but I can only get one to one. What am I doing wrong?

Residents Table
Room (Primary Key)
Fname
Lname
Phone
Email

Family Contact Table
Room (Primary Key)
Fname
Lname
Relationship
Address
Phone
Email

----------


## skhanal

You need to remove primary key on Room in contact table and create a foreign key on room which references room column in residents table.

----------


## Allan Murphy

Aliciaishot

Assuming a resident has many contacts

Residents Table
Resident_Ref (Primary Key)
Room
Fname
Lname
Phone
Email

Family Contact Table
Contact Ref (Primary Key)
Resident_Ref (Foreign Key)
Fname
Lname
Relationship
Address
Phone
Email


Do you have a Room table that shows the location and the facilites in the room etc.?
If you do have a room table then you will need to change Room to Room_ref so that it can be linkied to the room table

----------


## aliciaishot

Well first how do I create a foreign key? 

But what info would I want to put under resident_ref and then the contact_ref - a auto number?

I don't have a room table.

----------


## Allan Murphy

Hello Alicia

I have attached a sample database

The table structure is the same as my previous email.

A foreign key is a field in a relational table that matches the primary key column of another table. The foreign key can be used to cross-reference tables.

In the Contacts table I added a field called residents_ref. The field residents_ref is the Primary Key (PK) in the residents table and foreign key (FK) in the contacts table When you are adding the contaccts for the residents in the Contacts table you must also included the relevant resident_ref in the contacts table.

There is also a relationship diagram and a report that displays some data sorted by residents.

----------

