Table relationships

[Heather]

Hi there,

I am designing a database which tracks details of all
propspective recruitment candidates.

I have created a Candidate table which has contact details
and employment history for each person. I have set the
Primary Key as 'Candidate ID', an Auto-Number.

What I would like to do now is create another table to
track any contact made with these candidates. Ideally, I
would like to be able to open a record on the Candidate
form, then click a command button which would take me to
a 'Contact' form where I can enter contact made with that
particular person (may be multiple contacts made).

I have tried setting up a Contact table with 'Contact ID'
as the Primary Key (Auto Number) but can't seem to get the
tables to relate properly.

Can someone please suggest how I should be doing this?
Sorry about the long post, but this is driving me crazy!

 

[Tim Ferguson]

Ideally, I
would like to be able to open a record on the Candidate
form, then click a command button which would take me to
a 'Contact' form where I can enter contact made with that
particular person (may be multiple contacts made).

The simplest way to do this is for the contacts to live in a subform on the
candidates form.

I have tried setting up a Contact table with 'Contact ID'
as the Primary Key (Auto Number) but can't seem to get the
tables to relate properly.

The Contacts table needs a ContactID [1] to keep track of which contact you
are referring to, and a CandidateID field to keep track of which Candidate
this contact refers to.

You need to form a relationship between Candidates.CandidateID and
Contacts.CandidateID.

You would also use these two fields as Link Fields on the form and subform
to keep everything in step. After that, Access does all the hard work for
you.

HTH


Tim F

[1] A general plea not to use spaces and hashes etc in field or table
names, because although Access allows them, they still have to be handled
specially and one day you will run into trouble.