I'm not completely certain if this is the right section for this problem / question. I have a situation where I need to make sure that a field in one of my tables has a unique number under certain circumstances.

 

I have student information system which has a lottery module I built so that they can manage student applications for the coming year. Each student once entered into the database is assigned a lottery number and associated with a specific lottery which is associated with the school year and the grade they are applying for. When each student is entered they are given a lottery number, starting at 1.

 

The problem is I need to make sure that that number is not assigned again for the current lottery they are associated with. There may be other students assigned the number 1 (for instance) within that field but they are associated with lottery's of years past, so I cannot simply check the Require Unique Value option. It has to be unique based on the foreign key of the lottery they are associated with.

 

Any suggestions? I really appreciate any insight!

 

~ Chris

Is there a reason not to assign them unique numbers overall? There are enough numbers to go around...

 

 

It has to be unique based on the foreign key of the lottery they are associated with.

 

You say "foreign key" - does that mean there is a unique record for each lottery (or the entity that each lottery is associated with) in another table?

It is simply easier for the schools administration to keep track of it starting from 1 each year. The numbers would quickly go into the thousands and get annoying to track from them. It's hard to explain because I'd have to go into the lottery process for how children are chosen which is also prioritized by sibling, district, etc.

Yes there is another table that contains the lottery info for that year and grade. Example "2014-2015-k" for the kindergarten lottery for this upcoming school year.

Well, okay then. I think there are two aspects to this: (1) how to assign the numbers, and (2) how to validate their uniqueness. If the first one is solved correctly, then the validation is there just as an additional safety net. I am not sure if and how you solved the first aspect, so for now I will address the validation issue only.

 

You need to define a Text field and set it to auto-enter a calculated value (replacing existing data) =

LotteryNumber & " | " & fkYearGrade

where fkYearGrade is that foreign key field you mentioned earlier. Set this new field to validate as Unique, Validate always, Do not allow override.

Interesting. This sounds like it might be the solution. I will work to implement this and let you know how it goes. Thank you so much for you advice on this!