General solution for missing sequence 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 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.


--

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" wrote in message
...

snip

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.

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


"Chris2" pí¹e v diskusním
pøíspìvku ...

"Peter Danes" wrote in message
...

snip

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.

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


"Chris2" pí¹e v diskusním
pøíspìvku ...

"Peter Danes" wrote in message
.. .

snip

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.

Certainly that is one possible way to do it, but in this case I'm not
interested in ALL the missing numbers. I only want to know which one is the
next one available for use, either the first one in the first gap, or the
next one greater than all the ones used so far. This allows me to fetch that
one number with this single SQL statement, rather than looping through a
recordset with VBA. And a side benefit is that SQL is orders of magnitude
faster than such a VBA loop.

--
Pete



"David C. Holley" píse v diskusním príspevku
...
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 wrote:
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


"Chris2" pí¹e v
diskusním
pøíspìvku ...

"Peter Danes" wrote in message
.. .

snip

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.

"Peter Danes" wrote in message
...
"Chris2"

"Peter Danes" wrote in message
...

snip


snip



Sincerely,

Chris O.



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.

Yes, some cannot. In particular, a self-join cannot (as far as I know),
which is exactly what I used here. I'm not certain that I agree with the
notion that SQL is more readable with aliases than without, it seems to me
to be just one more re-direction that must be kept in mind when tracking or
debugging a statement. But that's just my opinion, worth exactly what you
paid for it. And I'm far from being a SQL expert, you may be right. I'd be
interested in your thoughts on how it improves readability.

And as for the question, well, I was just answering it - I didn't mean to
sound snippy. The way you worded it made me think that you really didn't
know what the difference between the statements was, but if you've been
writing SQL queries for years, you probably know more about it than I do.

--
Pete



"Chris2" pí¹e v diskusním
pøíspìvku ...

"Peter Danes" wrote in message
...
"Chris2"

"Peter Danes" wrote in message
...

snip


snip



Sincerely,

Chris O.



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" wrote in message
...
I'd be interested in your thoughts on how it improves readability.

Less text equals less to read equals greater readability. (See
below and consider.)

If the aliases are named correctly, then you automatically know what
tables they refer to.

Access, with it's penchant for re-arranging the SQL of queries,
especially for cutting out line-breaks, doesn't help much in the way
of readability, so it needs all the help it can get.

I'll admit most Access users don't care, as they use Design View
instead of SQL View. I use SQL View almost all the time.


Example: From a query in a thread (Group By Last, by Barrattolo_67).

Note: This is also a good example of why not to use spaces, as it
introduces masses of readability reducing brackets (not to mention
the other reasons).

Vanila MS Access Unmodified Query w/Spaces in Object Names:

INSERT INTO [Audit Follow-up Report] ( [No], [Thrust Area], [Title
of
Issue], [Risk Severity Code], Recommendation, [Responsible
Department],
[Management Action Plan], [Target Completion Date], [Revised Target
Date],
[Actual Completion Date], [Follow-up Status], [Change History],
[Management
Status Description], [Auditor's Comments] )
SELECT [tbl Comments].[Order of appearance], [tbl Comments].[Cycle
Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code],
First([tbl Comments].Recommendation) AS FirstOfRecommendation, [tbl
Management Responses].[Responsible Department], First([tbl
Management
Responses].[Management Action Plan]) AS [FirstOfManagement Action
Plan], [tbl
Management Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual
Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl
Management
Responses].[Completion Date Change History and Other Comments],
First([tbl
Follow-up Entries for Findings].[Management's Status Description])
AS
[FirstOfManagement's Status Description], First([tbl Follow-up
Entries for
Findings].[Auditor Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments] LEFT JOIN ([tbl Follow-up status codes] RIGHT
JOIN [tbl
Management Responses] ON [tbl Follow-up status codes].[Follow-up
status
order] = [tbl Management Responses].[Follow-up status code]) ON [tbl
Comments].[Comment Table counter] = [tbl Management
Responses].[Comment Table
counter]) LEFT JOIN [tbl Follow-up Entries for Findings] ON [tbl
Management
Responses].[ID for tbl Management Responses] = [tbl Follow-up
Entries for
Findings].[ID in tbl Management Responses]
GROUP BY [tbl Comments].[Order of appearance], [tbl Comments].[Cycle
Name],
[tbl Comments].[Comment Title], [tbl Comments].[Risk Severity Code],
[tbl
Management Responses].[Responsible Department], [tbl Management
Responses].[Target Completion Date], [tbl Management
Responses].RevisedTargetDate, [tbl Management Responses].[Actual
Completion
Date], [tbl Follow-up status codes].[Follow-up status code], [tbl
Management
Responses].[Completion Date Change History and Other Comments], [tbl
Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="FA-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];


Query Re-Aligned (note the line-breaks caused by the enormous length
of some of the lines.

INSERT INTO [Audit Follow-up Report]
([No]
,[Thrust Area]
,[Title of Issue]
,[Risk Severity Code]
,Recommendation
,[Responsible Department]
,[Management Action Plan]
,[Target Completion Date]
,[Revised Target Date]
,[Actual Completion Date]
,[Follow-up Status]
,[Change History]
,[Management Status Description]
,[Auditor's Comments])
SELECT [tbl Comments].[Order of appearance]
,[tbl Comments].[Cycle Name]
,[tbl Comments].[Comment Title]
,[tbl Comments].[Risk Severity Code]
,First([tbl Comments].Recommendation) AS
FirstOfRecommendation
,[tbl Management Responses].[Responsible Department]
,First([tbl Management Responses].[Management Action Plan])
AS [FirstOfManagement Action Plan]
,[tbl Management Responses].[Target Completion Date]
,[tbl Management Responses].RevisedTargetDate
,[tbl Management Responses].[Actual Completion Date]
,[tbl Follow-up status codes].[Follow-up status code]
,[tbl Management Responses].[Completion Date Change History
and Other Comments]
,First([tbl Follow-up Entries for Findings].[Management's
Status Description]) AS [FirstOfManagement's Status Description]
,First([tbl Follow-up Entries for Findings].[Auditor
Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments]
LEFT JOIN
([tbl Follow-up status codes]
RIGHT JOIN
[tbl Management Responses]
ON [tbl Follow-up status codes].[Follow-up status order] =
[tbl Management Responses].[Follow-up status code])
ON [tbl Comments].[Comment Table counter] =
[tbl Management Responses].[Comment Table counter])
LEFT JOIN
[tbl Follow-up Entries for Findings]
ON [tbl Management Responses].[ID for tbl Management
Responses] =
[tbl Follow-up Entries for Findings].[ID in tbl Management
Responses]
GROUP BY [tbl Comments].[Order of appearance]
,[tbl Comments].[Cycle Name]
,[tbl Comments].[Comment Title]
,[tbl Comments].[Risk Severity Code]
,[tbl Management Responses].[Responsible Department]
,[tbl Management Responses].[Target Completion Date]
,[tbl Management Responses].RevisedTargetDate
,[tbl Management Responses].[Actual Completion Date]
,[tbl Follow-up status codes].[Follow-up status code]
,[tbl Management Responses].[Completion Date Change History
and Other Comments]
,[tbl Comments].[Audit Report #]
HAVING ((([tbl Comments].[Audit Report #])="FA-BDI-04-34"))
ORDER BY [tbl Comments].[Order of appearance];


Query w/Table Aliases:

INSERT INTO [Audit Follow-up Report]
([No]
,[Thrust Area]
,[Title of Issue]
,[Risk Severity Code]
,Recommendation
,[Responsible Department]
,[Management Action Plan]
,[Target Completion Date]
,[Revised Target Date]
,[Actual Completion Date]
,[Follow-up Status]
,[Change History]
,[Management Status Description]
,[Auditor's Comments])
SELECT CO1.[Order of appearance]
,CO1.[Cycle Name]
,CO1.[Comment Title
,CO1.[Risk Severity Code]
,First(CO1.Recommendation) AS FirstOfRecommendation
,MR1.[Responsible Department]
,First(MR1.[Management Action Plan]) AS [FirstOfManagement
Action Plan]
,MR1.[Target Completion Date]
,MR1.RevisedTargetDate
,MR1.[Actual Completion Date]
,FU1.[Follow-up status code]
,MR1.[Completion Date Change History and Other Comments]
,First(FE1.[Management's Status Description]) AS
[FirstOfManagement's Status Description]
,First(FE1.[Auditor Comments]) AS [FirstOfAuditor Comments]
FROM ([tbl Comments] AS CO1
LEFT JOIN
([tbl Follow-up status codes] AS FU1
RIGHT JOIN
[tbl Management Responses] AS MR1
ON FU1.[Follow-up status order] =
MR1.[Follow-up status code])
ON CO1.[Comment Table counter] =
MR1.[Comment Table counter])
LEFT JOIN
[tbl Follow-up Entries for Findings] FE1
ON MR1.[ID for tbl Management Responses] =
FE1.[ID in tbl Management Responses]
GROUP BY CO1.[Order of appearance]
,CO1.[Cycle Name]
,CO1.[Comment Title]
,CO1.[Risk Severity Code]
,MR1.[Responsible Department]
,MR1.[Target Completion Date]
,MR1.RevisedTargetDate
,MR1.[Actual Completion Date]
,FU1.[Follow-up status code]
,MR1.[Completion Date Change History and Other Comments]
,CO1.[Audit Report #]
HAVING (((CO1.[Audit Report #])="FA-BDI-04-34"))
ORDER BY CO1.[Order of appearance];


Query w/out spaces in object names and w/out accompanying brackets
and w/out table object prefixes. I left the column aliases (for
output) alone.

INSERT INTO AuditFollowUpReport
(Nbr
,ThrustArea
,TitleOfIssue
,RiskSeverityCode
,Recommendation
,ResponsibleDepartment
,ManagementActionPlan
,TargetCompletionDate
,RevisedTargetDate
,ActualCompletionDate
,FollowUpStatus
,ChangeHistory
,ManagementStatusDescription
,AuditorsComments)
SELECT CO1.OrderOfAppearance
,CO1.CycleName
,CO1.CommentTitle
,CO1.RiskSeverityCode
,First(CO1.Recommendation)
AS [FirstOfRecommendation]
,MR1.ResponsibleDepartment
,First(MR1.ManagementActionPlan)
AS [FirstOfManagement Action Plan]
,MR1.TargetCompletionDate
,MR1.RevisedTargetDate
,MR1.ActualCompletionDate
,FU1.FollowUpStatusCode
,MR1.CompletionDateChangeHistoryAndOtherComments
,First(FE1.ManagementsStatusDescription)
AS [FirstOfManagement's Status Description]
,First(FE1.AuditorComments)
AS [FirstOfAuditor Comments]
FROM (Comments AS CO1
LEFT JOIN
(FollowUpStatusCodes AS FU1
RIGHT JOIN
ManagementResponses AS MR1
ON FU1.FollowUpStatusOrder =
MR1.FollowUpStatusCode)
ON CO1.CommentTableCounter =
MR1.CommentTableCounter)
LEFT JOIN
FollowUpEntriesForFindings FE1
ON MR1.IDForTblManagementResponses =
FE1.IDInTblManagementResponses
GROUP BY CO1.OrderOfAppearance
,CO1.CycleName
,CO1.CommentTitle
,CO1.RiskSeverityCode
,MR1.ResponsibleDepartment
,MR1.TargetCompletionDate
,MR1.RevisedTargetDate
,MR1.ActualCompletionDate
,FU1.FollowUpStatusCode
,MR1.CompletionDateChangeHistoryAndOtherComments
,CO1.AuditReportNbr
HAVING (((CO1.AuditReportNbr) = "FA-BDI-04-34"))
ORDER BY CO1.OrderOfAppearance;

It simply looks far more readable to me.

Access does mangle it right away after saving and closing the window
.. . . sigh /.

INSERT INTO AuditFollowUpReport ( Nbr, ThrustArea, TitleOfIssue,
RiskSeverityCode, Recommendation, ResponsibleDepartment,
ManagementActionPlan, TargetCompletionDate, RevisedTargetDate,
ActualCompletionDate, FollowUpStatus, ChangeHistory,
ManagementStatusDescription, AuditorsComments )
SELECT CO1.OrderOfAppearance, CO1.CycleName, CO1.CommentTitle,
CO1.RiskSeverityCode, First(CO1.Recommendation) AS
FirstOfRecommendation, MR1.ResponsibleDepartment,
First(MR1.ManagementActionPlan) AS [FirstOfManagement Action Plan],
MR1.TargetCompletionDate, MR1.RevisedTargetDate,
MR1.ActualCompletionDate, FU1.FollowUpStatusCode,
MR1.CompletionDateChangeHistoryAndOtherComments,
First(FE1.ManagementsStatusDescription) AS [FirstOfManagement's
Status Description], First(FE1.AuditorComments) AS [FirstOfAuditor
Comments]
FROM (Comments AS CO1 LEFT JOIN (FollowUpStatusCodes AS FU1 RIGHT
JOIN ManagementResponses AS MR1 ON
FU1.FollowUpStatusOrder=MR1.FollowUpStatusCode) ON
CO1.CommentTableCounter=MR1.CommentTableCounter) LEFT JOIN
FollowUpEntriesForFindings AS FE1 ON
MR1.IDForTblManagementResponses=FE1.IDInTblManagem entResponses
GROUP BY CO1.OrderOfAppearance, CO1.CycleName, CO1.CommentTitle,
CO1.RiskSeverityCode, MR1.ResponsibleDepartment,
MR1.TargetCompletionDate, MR1.RevisedTargetDate,
MR1.ActualCompletionDate, FU1.FollowUpStatusCode,
MR1.CompletionDateChangeHistoryAndOtherComments, CO1.AuditReportNbr
HAVING (((CO1.AuditReportNbr) = "FA-BDI-04-34"))
ORDER BY CO1.OrderOfAppearance;

But the left over results are still more readable than the original.


Sincerely,

Chris O.

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
http://I.Am/DougSteele
(no e-mails, please!)


"Peter Danes" wrote in message
...
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.


--

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.

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" 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
http://I.Am/DougSteele
(no e-mails, please!)


"Peter Danes" wrote in message
...
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.


--

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.