Adding a foreign key to a table

First you need to create the proper table structure in both relating tables.

For example

T_Contacts

************

ContactID

FirstName

LastName

TitleID (this would be the Foreign Key fromt T_ContactTitles)

...

T_ContactTitles

****************

TitleID (PK which acts as a FK in the T_Contacts table)

Title

Then in the Relationship editor, you add both tables, Click on the T_Contacts.TitleID field and then drag it on top of the T_ContactTitles.TitleID field.  This should create a basic relationship.  You can then double-click on the relationship link (the connecting line) and it will open a dialog in which you can enforce referential integrity, push updates, push deletions.

Hope this helps.

A Foreign Key is defined not by how the field is created, but by how it is used. A field becomes a Foreign Key when you use the Relationships Window to create a link between two tables, connecting the Primary Key of one table (the "one" side table) to a field of the same datatype and size in a second table (the "many" side table) and checking the "Enforce Referential Integrity" button.

Often (but not required) the Primary Key will be an Autonumber field; if so, you would need to have a field of Long Integer datatype in the child table. Different folks use different naming conventions; Access doesn't care what the fieldnames are, but you should have a standard naming convention that makes sense to you. For instance, if you have a table of Orders related one-to-many to a table of OrderDetails, you might have the autonumber primary key of Orders named OrderID, and a Long Integer field (NOT an Autonumber, and NOT the primary key) in the OrderDetails table; it could be named OrderID to match, or you might choose the convention of naming it fkOrder or fkOrderID.

The Lookup Wizard also creates relationships, but most of us would agree with you that it should be avoided. It's very limited and can cause as much confusion as benefit.

You have some great answers here.

However, your question remains  un-answered.

How are these column values set? What is the mechanical process as you ask!

How do you set this column value to relate back to the parent table?

It turns out that if you create a form based on this child table, when you add a record, then YOU must MANUALLY enter the value of that column used to relate back to the parent table. Or you can write some code to do this for you!

In other words, when you add a record in a form, you THEN must type in the value for this column used to relate back to the parent record. Or as  noted, have soe code set this value. 

Another way (recommended way) is to build a form with a sub form. When you use a sub-form, then for each child record you add, the FK (foreign key) value can be (will be) set for you automatically by Access.

And if you have a main form, and you have say a button on the form to launch a form based on the child table, then you will have to PASS the primary key value from that form. Or have the form look back to the other form.

So YOU will write code in the form based on this table of child records to correctly SET the FK value column to relate back to the one parent record in the parent table.

So the confusing part here is that when you setup a relationships, this “act” of setting up a relationship does NOT relieve you of the responsibility to setup and maintain that relationships with your code, or as noted letting Access set this FK value by using a form + sub-form arrangement.

So the FK column is in general a plane Jane long number type of column. This long number column thus must have a value set that points back to the parent record (the value you enter into this editable columns is the PK auto number ID of the parent record you wish to relate to).

And MOST important is then your code, your form etc. MUST set this value to attach the record to the parent record. In other words, either your code or your user will have to choose and type in this value.

So the “mystery” part here is that Access will NOT set the value of this column used to relate back to the parent record. (exception being a form + subform)

The above thus quite much suggest that YOU have to “choose” the parent record PK value to set – Access will not do this for you automatically, the exception being when you use a form + sub form. In the form + sub form setup, access can set the FK value by looking at the PK value currently set in the parent form.

The mechanical process:

In a form + sub-form, this setting is called the link master and link child settings. Once you set this, then for each child record you add, the parent record pk ID value will be pulled and placed into the FK. You can see/view this setting by clicking on a sub form control, and viewing properties.

If the two forms are separate, then in the second forms on-insert event, you can set the FK id in code like this

Me.tblecust_ID = forms!EditCustorm!id

Of course the above is for child records. For tables that drive a user set of choices, then a combo box is most often how users select such values.

Regards,

Albert D. Kallal (Access MVP)

Edmonton, Alberta Canada

Fields are added in Table Design view. You go to a blank line, type in the name you want for the field and then select a datatype. The datatype has to match the type of the Primary key it will be related to. Note, if the PK is an Autonumber you have to use Number>Long Integer. The name you assign will usually be the same as the PK it will be related to,

As John said a FK is defined by usage, nothing else. So if you add a field to a table that is intended to store the PK value for the related record in the related table then you have a foreign key. Nothing more is necessary. It is however, a good idea to formalize your relationships in the Relationships window so that you can impose Referential Integrity.  

So you DO "just type the name of the key in the "many" table". I suspect however you were trying to do this in Datasheet view instead of Table Design mode. I do not recommend adding fields that way. It does not give you any control over the properties of the field you are adding.

As it happens the StudentCourses example I gave you illustrates what you need to do, though not via DDL.  You'll find the file as StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

What this little demo file illustrates is a many-to-many relationship type between students and courses. A many-to-many relationship type is modelled by a third table which resolves it into two or more one-to-may relationship types.  You have a many-to-many relationship type between grantors and project types, so you would model it by a table which has two foreign keys, GrantorID and ProjectTypeID say which references the primary keys of Grantors and ProjectTypes respectively.  The primary key of this table is a composite one made up of the two foreign keys columns.  This type of table is sometimes colloquially referred to as a 'junction' table.

My demo illustrates a number of different interfaces for entering/editing data, but I'd strongly recommend the conventional form/subform set-up.  A project types form for instance would have a subform based on the table which models the many-to-many relationship type.  Alternatively the parent form could be a grantors for, depending on whether you want to enter grantors per project type, or vice versa.