Thank you, James. Always neat to learn another way of doing something.

--
Pete



"James A. Fortune" píse v diskusním príspevku
...
Peter Danes wrote:
I occasionally need to determine a number that I don't have in a
sequence,
either the first missing one in a gap in a set of sequential numbers or
the
next one in line at the end of a numbered series. Always it meant some
fumbling around, with either VBA at first or later with SQL when I got
good
enough at it, establishing the proper join parameters and such. For SQL
experts, this is probably routine and trivial, but for me it was always
a
bit of a chore. The last straw came with a database which I recently
wrote,
where the converted data had such a numbered series, and the owner
wanted to
be able to do both, fill in missing numbers in the gaps AND add new
numbers
at the end.

Walking home from a bar last night, I got to thinking about it and
realized
that both problems are actually fairly similar and that a simple and
general
solution is possible.
I put together a simple table containing one field with the following
entries:

1,2,3,4, 8,9,10, 15,16,17,18, 20, 22,23,24,25, 28,29,30

Missing a


5,6,7, 11,12,13,14, 19, 21, 26,27 and 31 on up.

This is the dataset used for all of the following examples.


Finding the next new number at the end of a series with SQL is trivial;
here
is a simplified version of a statement that I found somewhere in the
discussion groups a few years ago:

SELECT Max(MyTable.MySeqFld)+1 FROM MyTable;

This will return a one-record, one-field recordset containing exactly
one
value: 31, which is one greater than the largest value so far used in
that
field. This is what you would want to use instead of Access's
autonumber, if
the field is to contain meaningful sequence numbering, rather than just
a
unique identifier.

Locating gaps is a little more complicated: it involves a self-join from
N
to N+1 and finding where N+1 doesn't exist, indicating a gap at that
point.

SELECT MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL;

This generates a recordset of 5, 11, 19, 21, 26, 31, where each value is
the
first missing value in a gap, including the "open gap" at the end, and
that's where the trick to a general solution begins. Since these
situations
normally call for either the first (lowest number) gap or last (end of
recordset) gap, you need either the first or last record returned by
this
query. Sorting and using the TOP predicate gives you exactly that.

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY MT1.MySeqFld;

This will again return a one-record, one-field recordset containing
exactly
one value: 5, the first missing number in the first gap in the sequence.
Ascending sort order is the default, so the smallest number is the first
returned.

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY MT1.MySeqFld DESC;

This will return a one-record, one-field recordset containing exactly
one
value: 31, the same "one greater than the highest value so far used in
that
field" that is returned by the first simple example. Specifying the
descending order here is necessary, since we want the last (greatest)
record
from the set and Access SQL does not have a BOTTOM predicate.

Finally, an even more general statement can be used:

SELECT TOP 1 MT1.MySeqFld+1
FROM MyTable AS MT1 LEFT JOIN MyTable AS MT2 ON
MT1.MySeqFld+1=MT2.MySeqFld
WHERE MT2.MySeqFld IS NULL
ORDER BY ((INSTR("LF",[First or Last (F or L)]) *2)-3)*MT1.MySeqFld;

This expects one parameter, F or L and will return either the first
missing
number or the next number at the end of the line. The INSTR expression
evaluates to either -1 or 1 (or -3 if the parameter supplied is neither
F
nor L, but that has the same effect as -1 in this instance), that is
then
used as a multiplier for the sort field, so the sort is either by the
field
or by the negative of the field (or 3 times the negative of the field),
giving either ascending or descending order and with the TOP 1 predicate
again returns exactly the one value of interest.



An alternative SQL method using subqueries can be found he


http://groups.google.com/group/micro...4a5fee9ff13437

James A. Fortune

I was staying at a hotel and decided to go down to the pool. A 13 year
old bratty kid splashed water on me. When I told him to stop he said,
"You can't make me. I'm a minor. If you touch me you'll get in a lot
of trouble." I found a 17 year old at the pool and paid him $5 to hold
the kid underwater for 10 seconds. "How do you like me now?" -- Rob
Smith