How to find the gap?

[Günter Brandstätter]

Hi all,

I have a severe problem with a calculation on a table.
My table contains numbers from 1 to 9.999.999 in a field. Somewhere in this
table there may be a gap from let's say 2.000.000 to 3.000.000
The gap is not always on the same position.
I tried to find a procedure to display the two values (start and end of the
gap), but I did not find a solution.
I know it is easy to scan all records and find a number missing, but with
that amount of records I need a short way, because the time it takes for
scanning all records is too long.
Isn't there an mathematical solution for this problem? I tried to begin in
the middle of the recordset and find the maximum number from that point, if
it is equal greatest value in the field, i decrease the starting point and
find the maximum, and so on..... until I find the end of the gap. Then I go
from the end of the gap downwards and find the maximum value. This will be
the beginning of the gap.
This takes waaaaaaaaaaay too long time. I would need a shorter solution.

Any answer appreciated
Günter

 

[Dirk Goldgar]

Hi all,

I have a severe problem with a calculation on a table.
My table contains numbers from 1 to 9.999.999 in a field. Somewhere
in this table there may be a gap from let's say 2.000.000 to 3.000.000
The gap is not always on the same position.
I tried to find a procedure to display the two values (start and end
of the gap), but I did not find a solution.
I know it is easy to scan all records and find a number missing, but
with that amount of records I need a short way, because the time it
takes for scanning all records is too long.
Isn't there an mathematical solution for this problem? I tried to
begin in the middle of the recordset and find the maximum number from
that point, if it is equal greatest value in the field, i decrease
the starting point and find the maximum, and so on..... until I find
the end of the gap. Then I go from the end of the gap downwards and
find the maximum value. This will be the beginning of the gap.
This takes waaaaaaaaaaay too long time. I would need a shorter
solution.

Any answer appreciated
Günter

I posted a couple of answers to this question in
<microsoft.public.access>, where you independently posted it. You'd
have done better to cross-post it (listing multiple groups on the
"Newsgroups:" line), rather than multiposting it as you've done. Then
the message would appear in multiple newsgroups, but you'd see all
replies wherever you looked. This is more of a .queries question
anyway, it seems to me, though so far my code solution is more efficient
than my query solution.