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

How to assign the available number in a sequence.

P: 1
I am using Access 2007.

I have a table which has a field called priority which may or may not have a number value in it. I want to be able to click on this record in a form have have the lowest available number assigned to it. I know how to assign the next number, but can't figure out how to assign the lowest available number. What I mean is that records may have been deleted after a number was assigned to them, so now there are gaps in the sequence such as 1,2,3,5. In this example I want VBA to assign this record I have clicked on the number 4 as it is the lowest available number. Can anyone help?
Oct 29 '12 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 5K+
P: 5,397
Do you have a primary key?
Oct 29 '12 #2

Expert Mod 10K+
P: 12,366
Do a ranking query. Join the query to itself on the first row to the next row. Calculate the gap. Return the minimum number where the gap is greater than 1.
Oct 29 '12 #3

Post your reply

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