CODEWITHSUNDEEP
sqlms-accessms-access-2003
techindustry
techindustry

Reputation: 23

Find Missing Sequence Numbers in Access Table

I have a MS Access table in Access 2003 database named Comments and a column named Sequence Number in the Comments table. The Sequence Number column has numbers ranging from 1 to 20000. However, there are certain numbers missing from the Sequence Number column and I want to be able to view the numbers that are missing, e.g below I want to run a access query to see that 4 is missing. 

Sequence Number
 1
 2
 3
 5
 6

I'm using the following Access query in SQL view to get what I want.

SELECT ([Sequence Number]+1) AS MissingFrom, DMin("Sequence Number","Comments","Sequence Number>" & [Sequence Number]) AS MissingUntil
FROM Comments
WHERE (((DMin("Sequence Number","Comments","Sequence Number>" & [Sequence Number]))<>([Sequence Number]+1)));

However, when I run the query, I get the following error: 

Syntax error (missing operator) in query expression 'Min(Sequence Number)'.

Can someone please point out what is causing the query to fail? Thanks!

Upvotes: 0

Views: 1339

Answers (2)

forpas
forpas

Reputation: 164089

With NOT EXISTS:

SELECT MIN([Sequence Number]) + 1 
FROM Comments AS c
WHERE
  c.[Sequence Number] < (SELECT MAX([Sequence Number]) FROM Comments)
  AND NOT EXISTS (
    SELECT 1 FROM Comments
    WHERE [Sequence Number] = c.[Sequence Number] + 1
  )

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can get the first in a missing series using:

select num + 1
from comments
where num + 1 not in (select num from comments) and
      num + 1 <> (select max(num) from comments);

Upvotes: 1

Related Questions