search missing numbers

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?
--
Todd

Todd wrote:
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?


-----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/AwUBSqqwXYechKqOuFEgEQIq9gCg1JRpdCvC9W3DQMV2eRZqGA OO3T0AoOUx
OQPegvC5Hg7Km3ePxA7dvey0
=Hj9l
-----END PGP SIGNATURE-----

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


"Todd" wrote:

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?
--
Todd

Thanks! That did the trick.
--
Todd


"MGFoster" wrote:

Todd wrote:
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?


-----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/AwUBSqqwXYechKqOuFEgEQIq9gCg1JRpdCvC9W3DQMV2eRZqGA OO3T0AoOUx
OQPegvC5Hg7Km3ePxA7dvey0
=Hj9l
-----END PGP SIGNATURE-----

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


"MGFoster" wrote:

Todd wrote:
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?


-----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/AwUBSqqwXYechKqOuFEgEQIq9gCg1JRpdCvC9W3DQMV2eRZqGA OO3T0AoOUx
OQPegvC5Hg7Km3ePxA7dvey0
=Hj9l
-----END PGP SIGNATURE-----