Take the 2-minute tour ×

I made the following query for the SQL Server backend

SELECT TOP(1) (v.rownum + 99)
        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

share|improve this question
Have you done a google search or similar yet, there's tons out there on this, I've seen some questions on here about it as well, I haven't one to hand but won't be hard to find? –  Matt Donnan Oct 25 '12 at 14:02
I know I know.. I can't use autonumber cause this query is only for a default value. The user still can decide what the number is. Pass through queries are driving me mad so I just wanna look at other option if it's possible –  Marc Oct 25 '12 at 14:02
Yes I did a google and I have Never found one with an accepted answer. –  Marc Oct 25 '12 at 14:03
@TimSchmelter Yeah I read that, but I don't think rank is something I want. I don't wanna compare them. I just want the row number. –  Marc Oct 25 '12 at 14:17
@remou thank you remou! Here's my final query SELECT TOP 1 incrementNo-1 AS Expr1 FROM Proposals AS p WHERE (((p.IncrementNo)>101) AND ((Exists (SELECT v.IncrementNo FROM Proposals as v WHERE ((p.incrementNo-1 = v.IncrementNo) AND v.pyear = 11) ))=False) AND ((p.pyear)=11)) ORDER BY p.IncrementNo; It works like a charm. Unfortunately, I can't give you an accepted answer. I will upvote some of your answer so you still get points for your really appreciated help. –  Marc Oct 25 '12 at 15:42
show 11 more comments

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
add comment

Your Answer


