search missing numbers

  • Thread starter Thread starter Todd
  • Start date Start date
T

Todd

How can I make a query to search for missing sequential numbers. Say I have
a field called assetid and the assetid numbers start with 00001 and go
through 10000. How can I make a query to do a search and show all numbers
that are not listed from the above range of numbers?
 
I forgot to mention that I'm using access 2003 but the table I'm trying to
query is a linked table to an SQL 2005 dbase and the actual SQL column is
data type char(20) but when I view the design view in Access it is considered
text. All the assetid records are actual whole numbers but they start with
000001, 000002, 000003, etc.
 
Thanks! That did the trick.
--
Todd


MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Here's a query (changed to Access SQL) I got from an SQL Server
newsgroup:

SequenceTable is the table that is being searched for missing numbers.
Change the name to suit your set up. SeqNbr is the column (Field) being
tested - change to suit your set up.

SELECT LastSeqNbr
, NextSeqNbr
, LastSeqNbr + 1 As FirstAvail
, NextSeqNbr - 1 As LastAvail
, NextSeqNbr - (LastSeqNbr + 1) As NbrsAvail
FROM (
SELECT
(SELECT Nz(Max(Seq2.SeqNbr),0) As SeqNbr
FROM SequenceTable As Seq2
WHERE Seq2.SeqNbr < Seq1.SeqNbr) As LastSeqNbr
, SeqNbr As NextSeqNbr
FROM SequenceTable As Seq1
) as A
WHERE NextSeqNbr - LastSeqNbr > 1
ORDER BY LastSeqNbr

Resulting output

LastSeqNbr NextSeqNbr FirstAvail LastAvail NbrsAvail
0 10 1 9 9
103 105 104 104 1
107 114 108 113 6
116 129 117 128 12


HTH,
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSqqwXYechKqOuFEgEQIq9gCg1JRpdCvC9W3DQMV2eRZqGAOO3T0AoOUx
OQPegvC5Hg7Km3ePxA7dvey0
=Hj9l
-----END PGP SIGNATURE-----
Click to expand...
 
Do you happen to know where you got the SQL code for the below script? I
tried to put it in the sql dbase where the actual table is located and I got
an error and I was just wondering how different the code was in sql as
opposed to access.
 
Back
Top