Get the missing value in a sequence of numbers

ms-access, ms-access-2010, sql-server

Solution

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.

Problem

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

Original source