By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,929 Members | 1,447 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,929 IT Pros & Developers. It's quick & easy.

Help with allowing Duplicate records in certain conditions.

P: 3
Im creating a database for our Survivor NFL pool. The rules are Each person picks 1 team per week, they can not pick that team agian all season. Ive got two tables linked. The persons info table primary key is on their nickname linked to a another table for selecting their pick for that week. I want it to give an error if a person tries to select the same team more than one during the season. (indexed no duplicates) however when I use that parameter, it wont allow anyone else to use that team. Is there a way to make it so that others can select that team as well? Im using a drop down menu for team selection because I dont want anyone to accidentally or purposefully misspell the team name and potentially be able to pick the same team more than once. the dropdown combo box is pulling from a table with all NFL team names. Any advice would be appreciated
Nov 22 '11 #1
Share this Question
Share on Google+
5 Replies

Expert 5K+
P: 8,638
At the Table Level, you can create a Unique Index on the [Name] and [Team] Fields, preventing the kind of Duplication that you have described, but also allowing other individuals to select that Team. In this case, a Picture is worth a thousand words, so download the Demo, and then in Table Design View select: View ==> Indexes ==> View Details for the Index named 'Pick'.
Attached Files
File Type: zip NFL (12.3 KB, 57 views)
Nov 23 '11 #2

Expert Mod 15k+
P: 31,492
But the words win out if no-one looks at the pictures ADezii ;-)

Let me state that I'm sure you will get more than a full answer if you download the attachment (No worries in the least on that score). For those who don't have the time while trying to determine if this thread offers an answer to your, possibly very similar problem, read on.

Unique indexes can be set up very easily for individual fields in Access (The field properties allow you to specify such indexes). However, the table itself also allows specification and management of its indexes (View | Indexes) and in there you can specify multiple fields which, taken together as an entity, can be specified as unique.
  1. In this example case create a new index, giving the first row (only) a name in the Index Name column.
  2. In that same row in the next column (Field Name) select the field for the Person ID.
  3. In the same column, but the next row, select the field for the Team ID.
  4. Ensure the Unique property is set to Yes.

This will ensure that both the person and the team are available for reuse, but not together (IE. Not that team for that person).
Nov 23 '11 #3

P: 3
Thank you both, thi sis exactly what I needed.
Nov 23 '11 #4

Expert Mod 15k+
P: 31,492
We're both please to help :-) (It's why ADezii often goes that extra mile.)
Nov 23 '11 #5

P: 3
The added demo was a great visual aide, but I did understand by what you wrote too.
Thanks again!
Nov 23 '11 #6

Post your reply

Sign in to post your reply or Sign up for a free account.