Limit to one of a type of record

[wingforward]

I've got a table of People and a table of PhoneNumbers. PhoneNumbers can have a Type (Office, Fax, Mobile, etc) and be marked specifically as "Publish". People can have as many phone numbers as they like on record, but only one of the Type Fax and that is marked "Publish" and one of a non-Fax Type and marked "Publish".

How do I restrict data entry so those constraints are met?

Thanks,
DJ

 

[wilpeter]

I'm sure there's an easier way, but to start the ball rolling:
People table has personal data & an IDNo (primary).
Published table has IDNo (foreign key), PubPhone, Type, & PubFax
UnpublishedFax Table has UFaxIDNo (Primary), IDNo (foreign), UnpubFaxNo
UnpublishedPhone Table has no primary key, IDNo (foreign), UnpubPhoneNo, & Type.
The input form would be to People table with 3 subforms.

 

[milehighfreak]

I've got a table of People and a table of PhoneNumbers. PhoneNumbers can have a Type (Office, Fax, Mobile, etc) and be marked specifically as "Publish". People can have as many phone numbers as they like on record, but only one of the Type Fax and that is marked "Publish" and one of a non-Fax Type and marked "Publish".

How do I restrict data entry so those constraints are met?

Thanks,
DJ

Easy; create a seperate table for the publishable and non-publishable fax numbers, link them to the people, and index the numbers and people in each table with no duplicates. Create a many-to-many relationship for people-to-numbers, and one-to-one for the fax tables.

Even easier, include the fax numbers each as fields on the primary People table; since there's only one person for every person, they'll need to duplicate themselves in the People table if they want more than one of each.