Yes No Primary Key

[Aqif]

Hi

I have got a table of doctors and one table of Doctor Adresses with One to many relationship. In the doc addresses table one doctor can have many addresses. There is a Yes No field in Doctor addresses indicating Yes if that address is prefered address of not. What I want is that the user can only make one address as Yes and other as No as only one doctor address can be a preferred one. Is there any way to accomplish that in table design or possibly as form level?

Cheers
Aqif

 

[JimHorton]

Aquif,
Can't be done at table level, you need to write code somewhere to accomplish this. Without getting into the actual code, you'd just write a procedure, this would be in the Before Update of the control (or of the form), that counts the # of addresses with that docID who have Primary set True. If none, then this is either the first addr rec for that doc or none has been chosen as primary yet. If > 0, then prompt user that he's undoing an existing primary address, and does he really want to set this one as primary..blah, blah. If he choses yes, you do sql to clear the flag the other addr. This needs to be enforced on all areas where this flag might be set.
--Jim