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