Unique Value among Related Records

[Scott Hall]

I have a contacts database that includes a separate table for addresses. This allows me to have multiple addresses per client. The addresses are all related by the contact's unique ID#. I have also included a simple "flag" checkbox to mark which address is the contact's preferred "default" address.

However, I want that "default address" flag to be a unique value to all related records for that contact. So, they would only be able to have one address flagged at any one time. The validation options for making the flag "unique" apply to the whole table, not just the related records for that contact. So, if I use unique validation on the default flag, only one record in the database would be able to be flagged.

Two questions:

1) How do I set it up so that only one record (among a set of records with the same ID#) can be flagged with the Default Address checkbox?

2) If that is possible, is there also a way to make it behave, such that, if I flag one record for a contact as "default", that if that customer already has another address flagged as his/her "default", that it will automatically unflag that record and apply the flag only to the current record being flagged?

Let me know if this brings up further questions.

Thanks in advance for any information.

Scott

[comment]

I think a simple solution would be to have the "flag" in the contact record, for example by storing the selected AddressID.

[Scott Hall]

Thanks! This worked beautifully. I created a field in the contact database called "DefaultAddress" and another field in the addresses database called "DefaultAddressID" (a calculated field that created a unique ID based on client ID and the get record ID function). Now, when I click on the default address flag, it stores the "DefaultAddressID" in the contact record.

Again, thanks for the help!

Scott

[comment]

I would make this simpler by having an auto-entered serial number AddressID in the addresses database.