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 have posted this in the SQL forum, and I understand I have to code it in my front end (doing this in Access) so I wondered if any of you wonderful peeps could help me out with how to code it? I hope this makes some sort of sense to someone!!

Many thanks in advance

Robert Dolphin

for argument sake let say that you have a Prospect Table and a ProspectDetails Table. Will the ProspectDetails Table contain multiple instances of a specific ProspectID? If it does, then you need to INDEX the ProspectDetails.ProspectID with "Yes(Duplicates OK)" option(this is in Design View). You should also INDEX Prospect.ProspectID with "Yes(NO Duplicates)" option. Then you should establish a relationship between the two fields. Use the relationships window, establish a ONE-TO-ONE relationship between the two (you literally connect the two). Remember that you have to add the tables to the relationship window.

Now, I would suggest you lookup the ProductsID from the Prospect Table to fill up the ProspectDetails Table. You need to go into the Design window of ProspectDetails and go into the LOOKUP tab in the bottom. Set the following:

  Display Control = ComboBox
  Row Source Type = Table/Query
  Row Source = Prospect
  Limit to List = Yes

Let me know if this in the ball park.

"Life is a Database"

The normal access way it to use a Form / sub form design where the sub form properties have the master/child link fields set. Then Access will automatically take care of it for you.

Note: You must have the "key" field in the query for the form and sub form when working with an SQL back end.


Boyd
Access Based Accounting/Business Solutions developer.