Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30

    Unanswered: How to prevent duplicate fields

    Sorry about the vague title, but this is what I'm trying to achieve.

    I have a contact database for members of a club (name, address, etc.). One of the fields is set with a check box and contains a Yes/No value to indicate if the member is the club chairman.

    Obviously only one member can be chairman, so how can I ensure that when the box is checked for one member the field is set to No in all the other records?

    Thanks,

    Mike

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. Change the data type of the column containing the Yes/No value (Chairman) for: Data Type: Number, Field Size: Integer, Default Value: Null, Validation Rule: -1 Or Is Null, Required: No.

    2. Create an index on the table with: Index Name: uidx (whatever you want), Field Name: Chairman (the name of the column of data type Integer in 1.), Primary: No, Unique: Yes, Ignore Nulls: Yes.
    Have a nice day!

  3. #3
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    Quote Originally Posted by Sinndho View Post
    1. Change the data type of the column containing the Yes/No value (Chairman) for: Data Type: Number, Field Size: Integer, Default Value: Null, Validation Rule: -1 Or Is Null, Required: No.

    2. Create an index on the table with: Index Name: uidx (whatever you want), Field Name: Chairman (the name of the column of data type Integer in 1.), Primary: No, Unique: Yes, Ignore Nulls: Yes.
    Thanks for the soulution, which I have implemented. Unfortunately, if I try to select a new Chairman I now get an error window:

    "The changes you requested were not successful because they would create duplicate values in the index ...."

    Any ideas where it's gone wrong please?

    Mike

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    This error proves that you cannot assign the chairman status to two people anymore, which was the purpose of the modifications.

    Changing the chairman has become a two steps process:
    1. Change the True (-1) value from the column Chairman to Null for the row containing the ancient chairman.
    2. Set the same column to True (-1) for the row containing the new chairman.
    Have a nice day!

  5. #5
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    Sorry for being dense! You can tell I'm new to all this.

    OK, I understand what's happening but a one step process would be easier for the end user (who isn't me!). In other words, selecting a new Chairman by ticking their checkbox would automatically deselect the old Chairman. Is this possible?

    Thanks,

    Mike

    PS It appears that I can't use a checkbox to make this selection because clearing it gives 0 instead of Null which fails the validation rule??
    Last edited by P&T; 07-11-11 at 10:29.

  6. #6
    Join Date
    Aug 2006
    Posts
    126
    Another approach which I'd favor, would be to generalize your process by creating a table of Officers.
    The table has 2 fields, name of office and person (or person ID).
    In the table you identify in separate records the name (or person ID) of the Chairman, Secretary, Treasurer, Board Member 1, Board Member 2, etc. Then you can only have one name for each office, and you can do a lookup for any reports or on-screen forms.
    Later as you get more sophisticated, you could add term-of-office dates for each, and eventually have a multi-year history for each office.
    Have fun,
    Pete T
    PGT

  7. #7
    Join Date
    Nov 2009
    Location
    Berkshire, UK
    Posts
    30
    Thanks Pete, I'll have a look at that suggestion.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •