The final thing to conquer on my db...!
The user will rank various candidates (1-10) and I have to make sure that there is no duplicate rank assigned (unique to each user). How can I check to make sure that, for example, a user doesn't rank 2 different candidates with the same value? I know how to limit the rank range to the number of candidates and the min and max scores, but can't figure out how to keep duplicate rankings from occuring.
Thanks!
Full Version: How to check for duplicate data
Well, at the most basic level, it sounds like the answer would be to create a unique index which would prevent duplicates. It sounds like you have at least a ranking field and a user name field. So, if you created a unique index that incorporated both of these fields you should accomplish your goal. Does that make sense? 
I understand what you mean, but I am not sure where or how to do that... any hints?
Take a look at the attached screen shot.
1) Click the button circled in red. Alternatively, you can click View->Indexes. This will open a small window such as is shown in the picture.
2) Create a new index by making up a name on a blank line in this new window in the first column. In the second column pick one of the two fields that together makes up this unique index.
3) On the very next line, pick the other field that makes it unique in the second column leaving the first column blank. You should have something that looks like what is circled in blue, though with different names.
4) Click on the first line of this index (the line that has the name of the index in the first column). Make sure to select Unique as is circled in purple.
If you needed an index unique spanning 3 columns, you can continue this pattern. As long as you leave the "Index Name" column blank, it will continue from the line above for the same index.
Let me know what, if anything, doesn't make sense and we'll go from there.
Edited by: babrandt on Wed Sep 7 17:25:56 EDT 2005.
1) Click the button circled in red. Alternatively, you can click View->Indexes. This will open a small window such as is shown in the picture.
2) Create a new index by making up a name on a blank line in this new window in the first column. In the second column pick one of the two fields that together makes up this unique index.
3) On the very next line, pick the other field that makes it unique in the second column leaving the first column blank. You should have something that looks like what is circled in blue, though with different names.
4) Click on the first line of this index (the line that has the name of the index in the first column). Make sure to select Unique as is circled in purple.
If you needed an index unique spanning 3 columns, you can continue this pattern. As long as you leave the "Index Name" column blank, it will continue from the line above for the same index.
Let me know what, if anything, doesn't make sense and we'll go from there.
Edited by: babrandt on Wed Sep 7 17:25:56 EDT 2005.
Is there a way to change the message so it is not that long one if they user enters a duplicate? Something simply like "you cannot assign the same value to 2 different people".
Sure. Don't think there's any way that I know of to do that if you're entering data right into a table. However, assuming you're using a form, you can use the Before Update event. In that event, see if entering the data would cause a violation. If so, present your user-friendly message and set the Cancel argument of the BeforeUpdate event to True (or a non-zero integer).
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.