General solution for missing sequence numbers

[Peter Danes]

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 are:


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.


--

Pete

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

 

[Chris2]

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.

Peter Danes,

I am not sure what the difference is between the above and the
below.

SELECT MAX(MT1.MySeqFld) + 1
FROM MyTable AS MT1;


Sincerely,

Chris O.

 

[Douglas J Steele]

You might be interested in the analysis I had in my April, 2004 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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 are:


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.


--

Pete

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

 

[David C. Holley]

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

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 are:


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.

 

[Peter Danes]

There are three differences:

1. Your example is the same as my first example which returns only the
"greatest +1", except that you additionally include an alias to the table,
the "AS MT1" at the end of the statement. It doesn't hurt anything, but
isn't really necessary.

2. Youe example doesn't call for a parameter, mine does, to determine the
sort order and so whether you get the first missing number or the next in
line greater than all numbers used so far.

3. Obviously, the example you posted is considerably simpler, and if you
only need what it returns, simpler is preferable. The point of my 'lecture'
was simply that a general solution to these related problems is possible
with a single SQL statement. I do not claim that it is preferable in all
situations, or even any particular situation.

Pete

 

[Peter Danes]

Thank you Doug, interesting article. I like your addition of the range, I
think I'll be able to use that in something I'm working on now. And many of
the other titles look intriguing as well - time to do some reading.

(BTW, the description for invoice 11 says how about sending me an e-mail,
but your signature says no e-mails, please. I'm feeling schizophrenic. Maybe
if I write you one but don't send it...?)

Pete

You might be interested in the analysis I had in my April, 2004 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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 are:


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.


--

Pete

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that all may
benefit from the exchange of ideas.

 

[Peter Danes]

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

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

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 are:


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.

 

[Douglas J Steele]

I'm sorry, I don't understand what you mean by "invoice 11"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Doug, interesting article. I like your addition of the range, I
think I'll be able to use that in something I'm working on now. And many of
the other titles look intriguing as well - time to do some reading.

(BTW, the description for invoice 11 says how about sending me an e-mail,
but your signature says no e-mails, please. I'm feeling schizophrenic. Maybe
if I write you one but don't send it...?)

Pete

You might be interested in the analysis I had in my April, 2004 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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 are:


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.


--

Pete

This e-mail address is fake to keep spammers and their auto-harvesters out
of my hair. If you need to get in touch personally, I am 'pdanes' and I use
Yahoo mail. But please use the newsgroups whenever possible, so that

all
may

benefit from the exchange of ideas.

 

[David C. Holley]

Have you thought about using a DAO approach where you loop through the
records one by one and compare the current value to the previous?

 

[Peter Danes]

Small joke. (Very small) In the sample database for the article to which you
referred me, you have two tables, PossibleInvoices and Invoices, one with
just numbers and one with numbers and a text field. In the text field next
to invoice number 11, you had this comment "If so, how about sending me an
e-mail?"

Sorry for the confusion.

Pete

I'm sorry, I don't understand what you mean by "invoice 11"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Doug, interesting article. I like your addition of the range, I
think I'll be able to use that in something I'm working on now. And many of
the other titles look intriguing as well - time to do some reading.

(BTW, the description for invoice 11 says how about sending me an e-mail,
but your signature says no e-mails, please. I'm feeling schizophrenic. Maybe
if I write you one but don't send it...?)

Pete

You might be interested in the analysis I had in my April, 2004 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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 are:


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.


--

Pete

This e-mail address is fake to keep spammers and their
auto-harvesters
out
of my hair. If you need to get in touch personally, I am 'pdanes' and
I
use
Yahoo mail. But please use the newsgroups whenever possible, so that all
may
benefit from the exchange of ideas.

 

[David C. Holley]

As soon as I posted that I realized that there are situations where
sequential numbers are needed - like a hotel where you're dealing with
room numbers. (That's a particular OUCH! since I worked in resort
operations for 9 years.)

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

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

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 are:


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.

 

[Douglas J Steele]

Finally, someone who read the sample data! <g>

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Small joke. (Very small) In the sample database for the article to which you
referred me, you have two tables, PossibleInvoices and Invoices, one with
just numbers and one with numbers and a text field. In the text field next
to invoice number 11, you had this comment "If so, how about sending me an
e-mail?"

Sorry for the confusion.

Pete

I'm sorry, I don't understand what you mean by "invoice 11"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Doug, interesting article. I like your addition of the range, I
think I'll be able to use that in something I'm working on now. And

many
of

the other titles look intriguing as well - time to do some reading.

(BTW, the description for invoice 11 says how about sending me an e-mail,
but your signature says no e-mails, please. I'm feeling schizophrenic. Maybe
if I write you one but don't send it...?)

Pete


"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> pí¹e v diskusním
pøíspìvku You might be interested in the analysis I had in my April, 2004 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can download
the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


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 are:


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.


--

Pete

This e-mail address is fake to keep spammers and their
auto-harvesters
out
of my hair. If you need to get in touch personally, I am 'pdanes' and
I
use
Yahoo mail. But please use the newsgroups whenever possible, so that all
may
benefit from the exchange of ideas.

 

[David C. Holley]

Keep your day job.
(FYI - You probably don't want to see some of the remarks that I've left
in my code)

Small joke. (Very small) In the sample database for the article to which you
referred me, you have two tables, PossibleInvoices and Invoices, one with
just numbers and one with numbers and a text field. In the text field next
to invoice number 11, you had this comment "If so, how about sending me an
e-mail?"

Sorry for the confusion.

Pete

I'm sorry, I don't understand what you mean by "invoice 11"

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Thank you Doug, interesting article. I like your addition of the range, I
think I'll be able to use that in something I'm working on now. And many
of

the other titles look intriguing as well - time to do some reading.

(BTW, the description for invoice 11 says how about sending me an e-mail,
but your signature says no e-mails, please. I'm feeling schizophrenic.
Maybe

if I write you one but don't send it...?)

Pete


"Douglas J Steele" <NOSPAM_djsteele@NOSPAM_canada.com> pí¹e v diskusním
pøíspìvku
You might be interested in the analysis I had in my April, 2004 "Access
Answers" column in Pinnacle Publication's "Smart Access". You can
download

the column (and sample database) for free from
http://www.accessmvp.com/djsteele/SmartAccess.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



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 are:


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.


--

Pete

This e-mail address is fake to keep spammers and their
auto-harvesters

out

of my hair. If you need to get in touch personally, I am 'pdanes' and
I

use

Yahoo mail. But please use the newsgroups whenever possible, so that
all

may

benefit from the exchange of ideas.

 

[John Vinson]

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.

I agree that Access' autonumber isn't suitable in such cases... but
there are real, major problems with "filling in the gaps."

How did the gap get there in the first place? Presumably a Sample #312
was entered at some point, and then deleted: the entry was found to be
erroneous, misnumbered, or for some other reason had to be removed.
New entries would go in up at Sample #844 but you now have this gap.

OK... fill in the gap then. BUT!

What if there's a publication referring to the (erroneous) old Sample
312, and you now assign a DIFFERENT sample the same number? What if
someone has 312 written down on a Post-It note as "check up on this
really interesting sample" - or noted in their memory? Sure, you can
change it in the database; but where *else* does the information
exist, and can you change *that*?

John W. Vinson[MVP]

 

[David C. Holley]

To summarize, I believe that the general question is - What is happening
that is causing the gaps to occurr?

 

[James A. Fortune]

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 are:


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 here:

http://groups.google.com/group/microsoft.public.access/msg/094a5fee9ff13437

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

 

[Rob Oldfield]

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.

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

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

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 are:


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.

 

[David C. Holley]

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.

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.

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

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 are:


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.

 

[Chris2]

There are three differences:

1. Your example is the same as my first example which returns only the
"greatest +1", except that you additionally include an alias to the table,
the "AS MT1" at the end of the statement. It doesn't hurt anything, but
isn't really necessary.

Using table aliases may not be necessary, but I haven't written a
query more complicated than SELECT * FROM <table_name> in years
without them. The readability of SQL is greatly improved by their
use, and some queries cannot be written without them.

In any event, table aliases were not the purpose of my post.

I was only asking a question.


Sincerely,

Chris O.

 

[Peter Danes]

Hello John.

The gaps got there in the first place because the owner of the database is a
scientist, not a computer tech. As I explained in response to David's post,
she was using the record number that appears in a form's navigation text box
as an identifier, and trying to keep that synchronized with an autonumber
field. After discovering that a deleted record, even at the end, did not
re-use the next available number (the standard autonumber lament of
beginners) she went at it by erasing text from the individual fields and
entering new text without actually deleting the record. But by the time she
discovered this kludge, she already had gaps in the numbering. I had quite a
time convincing her that the record number in the navigation box is not an
identifier and that her whole approach to this was not the best way to
handle the numbering issue.

As far as the problem of references to specific numbers in other places than
the database, you're right, but it's not something I can address. This is
the way their system works, they like it that way and want it left alone.
And in this case, I don't believe it's really a problem. Most of the gaps
are places where something was written down incorrectly, inadvertently
erased and the old autonumber field wouldn't let them use the number again.
Or some numbers were not used at all - there was a conversion somewhere
along the line, trying to get the record number back in sync with autonumber
and someone managed to sling in a gap of 230 unused numbers. I'm not sure
quite how they managed that, when I asked about it and what they had done,
the general response was that they didn't know what they had done, it just
somehow turned out that way. Again, these are scientists, not computer
techs. In any case, this dataset is what I got handed and I had to do what I
could to accomodate their needs. A better identifying scheme is certainly
not difficult to imagine, but I can't really expect them to drop what
they're doing and go renumber their entire collection just to suit my
technical preferences.