Get the missing value in a sequence of numbers

Question

I made the following query for SQL Server backend

SELECT TOP(1) (v.rownum + 99) FROM ( SELECT incrementNo-99 as id, ROW_NUMBER() OVER (ORDER BY incrementNo) as rownum FROM proposals WHERE [year] = '12' ) as v WHERE v.rownum <> v.id ORDER BY v.rownum 

to find the first unused offer number. (This is not about the last post +1)

But I realized that ROW_NUMBER not supported in access. I looked and I can not find something like that.

Does anyone know how to get the same result as ROW_NUMBER on access?

Maybe there is a better way to do this.

Actually, people insert their sentence No (incrementID) without restrictions. This number is similar to 13-152. xx- for the current year, and -xxx - offer number. The last 3 digits must be incremental, but in some cases, maybe 10 times a year they have to skip some digits. Therefore, I cannot have auto-increment.

So, I make this request, so when they open the form, the default number is the first unused .

How it works:

Since the number starts at 100, I do -99, so it starts at 1.

Then I compare the line number with the identifier so that it looks like this:

 ROW NUMBER | ID 1 1 (100) 2 2 (101) 3 3 (102) 4 5 (104)<--------- WRONG 5 6 (105) 

So, now I know that we are missing 4. So I'm coming back (4 - 99) = 103

If there is a better way, I am not against change, but I really like this request.

If there is no other way, and I can not simulate the line number in the access, I will use the pass through the request.

thanks

Answer 1

From your question, it turns out that you are looking for a space in a sequence of numbers, therefore:

 SELECT b.akey, ( SELECT Top 1 akey FROM table1 a WHERE a.akey > b.akey) AS [next] FROM table1 AS b WHERE ( SELECT Top 1 akey FROM table1 a WHERE a.akey > b.akey) <> [b].[akey]+1 ORDER BY b.akey 

Where table1 is the table and akey is the serial number.

Answer 2

 SELECT T.Value, T.next -1 FROM ( SELECT b.Value , ( SELECT Top 1 Value FROM tblSequence a WHERE a.Value > b.Value) AS [next] FROM tblSequence b ) T WHERE T.next <> T.Value +1