Find Missing Sequence Numbers in Access Table

0
votes

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!

2
sqlms-accessms-access-2003
Field Sequence Number has a space and therefore must be enclosed in [ ], even within domain aggregate function argument. However, weird thing is, a table or query name with space does not need [ ] in domain aggregate function argument. Use of punctuation/special characters will also require [ ] to delimit object names. Also, reserved words as names can cause issue. Suggest avoid those features in naming convention. - June7

2 Answers

2
votes

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
  )
1
votes

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);