Foreign Key

Hi Valerie,

I have been working through some posts I can anser working from the top of the list so answered Sara's almost identical question first, so you may want to see my answer there. As you ask how to use it I will continue from my answer to Sara here.

So foreign key is the key in another table that provides the link to 'this' one, hence the name foreign.

Establish in your mind which table will be the parent and which one the child, ie which one will have more than one record related to each in the other. Which ever has the many records will have the foreign key. To use it link it to the primary key in the one side of the relationship.

So for albums and tracks, create an Albums table with a field AlbumTitle and save the table letting access create a primary key for you. Re-open it and (in design view) re-name the ID field to Album ID. Then close and save this table.
Create a Tracks table and create your own field called AlbumID and set this to Number type (leaving it in the default Long Integer type). Create another field in the tracks table called TrackTitle (as the default text type). Close and save the tracks table and let access create a primary key for this table for you.

Go to relationships view. Right click and add the two tables to the view (then close the little add in table window). So you see your two tables. Albums has AlbumID as its primary key and Tracks has AlbumID as foreign key.

Drag one AlbumID field on to the other (any order) and a link dialogue window appears, Select enforce integrity and the click create. The link line will apear and the symbols '1' and the infinity symbol will show which side is the 'one' (1) and which side is the many (infinity) of the relationship.

If you place the mouse pointer carefully exactly on the link line you will be able to right click and select Edit Relationship and for the case of albums and tracks I would go to Join Type and select Option 2 so that you can enter albums before you enter track information, or just not have any track listed for some albums if you choose but you will still want to see those albums listed.

In raw table form you can enter one or more album to start with, then in the tracks table enter the album id to which the track belongs then enter the title of the track. If you try entering a track first access wont let you get away with that because it wants to ensure for your sake that each track knows to which album it belongs so there must be an album to relate to first. (Like you have to have a parent for a child to be born). The enforcement you have set with Enforce Integrity has done this. The enforcement is still present when you create a form to enter it but your form design would prpably lead you to enter album title first as you would place that field near the top perhaps. I would recommend having the tracks as a tabular or datasheet subform within the album singular form. (Eg Choose Justified form when creating the album form).

If you understand the above or part of it, you will be more prepared for the intermediate course then I was!

Regards, Mark (delegate)