Re: find missing numbers
- From: "Tom Ellison" <tellison@xxxxxxxxxxx>
- Date: Wed, 24 May 2006 07:25:39 -0500
Dear MH:
Use a subquery:
SELECT CLng(YourColumn) + 1 AS Missing
FROM YourTable T
WHERE NOT EXISTS(
SELECT *
FROM SomeTable T1
WHERE CLng(T1.YourColumn) = CLng(T.YourColumn) + 1)
This will report the first missing number of each sequence. You can add a
similar subquery to find the high end of each missing sequence. Finally,
you would probably want to omit the sequence starting ath the MAX() value of
this column which has not upper limit.
For a complete query in my response, provide your table name and column
name.
Note that it is probably a bad practice to store values that are numeric in
a text column. It makes work like this extra difficult, and cannot be
indexed to obtain decent performance. This is going to be horribly slow
given any large set of data. If the column were numeric and indexed, it
wouldn't be bad.
Tom Ellison
"mhmaid" <mhmaid@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:DE92BEAD-46AE-477C-B0F5-79759EA3A6AE@xxxxxxxxxxxxxxxx
Is it possible for access to find missing numbers
ie. i have cpv numbers 1 2 3 4 etc I need to know if there is any missing
number in the sequence. this is not autonumber , it is a text field.
1 2 3 6 ( number 5 is missing )
i want a Query to display missing numbers , or , to tell that there is
break
in the sequence so and so.( out of sequence)
.
- Prev by Date: Re: Query on Queries
- Next by Date: Query parameters for search
- Previous by thread: Re: find missing numbers
- Next by thread: Re: find missing numbers
- Index(es):
Relevant Pages
|
