find missing numbers

[Guest]

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)

 

[John Spencer]

Simplest query would be

SELECT Val(T1.CPV) +1 as StartGap
FROM Table as T1 LEFT JOIN Table as T2
ON Val(T1.CPV) = Val(T2.CPV)+1
WHERE T2.CPV Is Null

That will report that 7 is missing if all your values are 1,2,3,4,5,6. That
is if the highest value is 6, then 7 is missing.

 

[Tom Ellison]

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

 

[Jamie Collins]

SELECT Val(T1.CPV) +1 as StartGap
FROM Table as T1 LEFT JOIN Table as T2
ON Val(T1.CPV) = Val(T2.CPV)+1
WHERE T2.CPV Is Null

You should take care with such join conditions. The VAL function
returns a double float value e.g.

SELECT TYPENAME(VAL('1'))

Floating point values are not appropriate for equality comparisions
(google it).

Jamie.

--

 

[Guest]

thank for response and help
my table name "expense reports"
field name"vouchernumber"
number format"00/000" i.e. yy/number
currently is text field.
thank again

 

[Tom Ellison]

Dear MH:

Well, 00/000 is not a number. I take it, then, that these two parts are in
one column, as text. That is VERY BAD database design. Put them in two
numeric columns instead. You can always format them and concatenate them
when you want the appearance you need.

So, I start with a query to help with this task.

SELECT Int(LEFT(vouchernumber, 2)) AS yy,
Int(Right(vouchernumber, 3)) as num
FROM [expense reports]

You may want to add other columns to this. Create a new table with separate
columns for yy and num. These columns should be indexed, perhaps together
in a single index, unique if that is appropriate.

From this, you should be able to provide some information as to missing
numbers. There are two ways.

The first is a bit easier, but it will give missing ranges of numbers. For
example, of the series is:

1, 2, 3, 6, 11, 43

Then the missing numbers can be reported as:

4-5
7-10
12-42

If you want a list of all 37 missing numbers, one per line, then the second
method will be necessary.

I would point out that there would potentially be a separate list of missing
numbers for each value of yy.

Here's the query for that first one, based on the altered table, or if you
wish, on the query I gave above. I'll call that query or table NewVoucher.

SELECT yy,
vouchernumber + 1 AS StartMissing
FROM NewVoucher T
WHERE NOT EXISTS (
SELECT *
FROM NewVoucher T1
WHERE T1.yy = T.yy
AND T1.vouchernumber = T.vouchernumber + 1)
AND vouchernumber < (SELECT MAX(vouchernumber)
FROM NewVoucher T1
WHERE T1.yy = T.yy)

If this is desirable, I can elaborate on how to return the value of the end
of each range of missing numbers.

The alternative that gives every missing number for each yy would require a
table Numbers with, at a minimum, all values from 1 to 999 in a column. You
can RIGHT JOIN to this and filter to where the NewVoucher.num is NULL.
Those would be the missing ones. Use a MAX limit as in the above as well.

There likely a lot more to this, but I'm choosing not to elaborate on each
and every alternative at this point. Give me some direction so I can move
toward the direct solution of your specific need.

Tom Ellison

 

[John Spencer]

yes, you are right. I did assume that the numbers were whole numbers since
the sample data that was posted was only whole numbers (integers).

I see from later postings that the user actually has values that are more
YY/nnn. where nnn is a series of number characters. Tom Ellison is working
the problem with the poster, so I'm outta here.

 

[Guest]

dear Mr Tom,
Thank you very much for your help
I have Tried that and found it working ok, the only thing is , some of the
missing numbers are shown twice in the list .
thanks again

 

[Tom Ellison]

Dear MH:

That could happen if a number occurs twice in the list. Is this the cause?
If so, a DISTINCT could be used to stop that.

Tom Ellison

 

[Jamie Collins]

I did assume that the numbers were whole numbers since
the sample data that was posted was only whole numbers (integers).

My wider point is: if you know they are integers then don't cast them
as double float.

Jamie.

--

 

[Guest]

Yes it is the cause.
i have implemented the " DISTINCT" and now it is ok

thank your for your help.