I have a table which contains all our prospect data. each prospect is uniquely identified by a prospect number. I want to create a second table containing data specific to a particular area of our business but still on a per prospect basis. I realise I have to create a relationship between the two tables using the field ProspectNo. the thing is, how can I have SQl/Access (which is what I'm using for my front end) ensure that a) existing prospect numbers appear in the new table, and b) new prospects receive an entry in both tables. I hope this makes some sort of sense to someone!!

Many thanks in advance

Robert Dolphin

Nothing is going to do this automatically.. YOu have to code for it in your access app. The relationship just ensures data integraty.

Damn, nothings ever easy. Ok, so next question, how do I go about coding it?

You will have to create Insert statements. First insert into your child table, then insert into the parent table. I don't know access, but I am sure it is easy enough.