0

I am trying to write an algorithm which, when run, will go through a table in my access database and find the lowest number (=> 1) that is unused. For clarification, this is to be an ID for records and I can't perform the tasks I will need to if I use the Autonumber function inside access.

For example, if I have records with IDs of 1 and 3; the algorithm will detect that "2" and will then return a value of 2 which I will be able to use later.

Unfortunately I don't have any example code which I can put up, but I am using Microsoft Visual Studio 2010 and I am programming in VB.NET with the Oledb system to provide a connection with my database.

Thank you for any help.

EDIT: I was considering using a SORT query but I don't know what I would follow it up with. So while it would be very rudimentary thus far, I could begin the algorithm with:

SELECT StudentID FROM Students
ORDER BY StudentID;

I suppose that by doing this I could get the user to find an ID themselves and then set it, but it may defeat the purpose of my application to provide an automated system for tracking student data.

Will Swindell
  • 15
  • 5
  • May be you are look for the same solution: [enter link description here](http://stackoverflow.com/questions/1320448/how-to-find-unused-id-in-a-column) – Steven Jan 12 '17 at 10:47
  • @Steven Yeah, that's right. Couldn't find that question when I was researching beforehand. Thank you! – Will Swindell Jan 12 '17 at 10:54

1 Answers1

0
select min(StudentID) + 1
from Students s1
where not exists (select 1 from Students s2
                  where s2.StudentID = s1.StudentID + 1)
jarlh
  • 42,561
  • 8
  • 45
  • 63
  • I'd like to ask what is meant by "t1", does it mean the column name? – Will Swindell Jan 12 '17 at 10:51
  • t1 and t2 are table aliases. (To distinguish between the two instances.) – jarlh Jan 12 '17 at 10:54
  • So with my column containing ID named "StudentID" and my table "Students" I would have `select min(StudentID) + 1 from Students t1 where not exists (select 1 from Students t2 where t2.StudentID = t1.StudentID + 1)` ? – Will Swindell Jan 12 '17 at 10:58
  • I see the edit, thank you very much. I +1'd but my rep isn't high enough to display so I thought I'd write a thank you instead. – Will Swindell Jan 12 '17 at 11:03