Get the missing value in a sequence of numbers

I made the following query for the 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 proposal number. (It's not about the lastrecord +1)

But I realized ROW_NUMBER is not supported in access. I looked and I can't find something similar.

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

Maybe there's a better way of doing this.

Actually people insert their proposal No (incrementID) with no constraint. This number looks like this 13-152. xx- is for the current year and the -xxx is the proposal number. The last 3 digits are supposed to be incremental but in some case maybe 10 times a year they have to skip some numbers. That's why I can't have the auto increment.

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

How it works:

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

Then I compare the row number with the id so 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 skip 4. So I return (4 - 99) = 103

If there's a better way, I don't mind changing but I really like this query.

If there's really no other way and I can't simulate a row number in access, i will use the pass through query.

Thank you

回答1:

From your question it appears that you are looking for a gap in a sequence of numbers, so:

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 sequenced number.

回答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