Because doing things by hand leads to errors. If I can give the user a tool
to that automatically informs them which number is the next one available
and sets it as the default in the ID number field, why should they have to
determine it manually for every entry and risk an error? That sort of
situation is exactly how such gaps in numbering could easily arise.

--
Pete



"David C. Holley" píse v diskusním príspevku
...
So why not simply create the sample numbers by hand and then enter them?
I would hope that any scientist capable of research would also be
capable of counting from 1 to 1000.

Rob Oldfield wrote:
In that situation the issue isn't about how to 'fill in the gaps', it's
how
to explain to a particular group of scientists that they don't have a
clue
about how relational databases work. Are they really stupid enough to
not
understand that, although my highest ID number is 10000, that I only
have
1000 samples? Even if I point out that 9000 of those records are marked
as
'not really a sample' (i.e. the idea of disallowing deletions and
marking
the record as inactive instead)?

Expecting sequential numbering is just wrong. It's up to those of us
who
actually know about the issues raised by John Vinson to not allow those
who
don't to tell us how to put databases together.


"Peter Danes" wrote in message
...

Such situations are common, for a variety of reasons. Depends on the
database and the user and what the data is for. The particular example

that

inspired this outburst is a mycological database, where the numbers are

used

to sequentially number the scientist's samples. She told me that
numbering
is important for others in the field to know roughly how many samples a
particular researcher has, and for internal inventory purposes, that
they
don't expect to have holes in the numbering sequence.

If someone who has 1,000 samples in their collection publishes something
about their sample number 10,000 and it is known that the person does
not
have anywhere near 10,000 samples, it would be viewed as odd at the very
least, possibly unethical and such a person would find himself not taken
seriously by other researchers. One or two numbers amiss in this
situation
is obviously not a major concern.

And for the internal inventory controls, if someone sees sample 152 next

to

150, they are going to wonder where is number 151. The inventory methods
used expect sequential numbering and a missing number is an indication
of
something wrong. For inventory numbers in the original database, she
used
the record number that appears in the text box of Access's navigation
control in conjunction with an autonumber field. You may guess what sort

of

hash resulted from that. I started out trying to fix a few things for
her
and wound up doing almost a complete re-write of the entire thing and
this
numbering issue is one of the things that surfaced. She wants to be able

to

fill in all the gaps as well as add new numbers to the end as she
collects
new samples.

Pete


"David C. Holley" píse v diskusním príspevku
.. .

What is the specific *NEED* to find the missing numbers?

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.