Finding numbers not in a sequence

[BDennis]

Is there a simple way of finding the next sequential number which is NOT in a number field of a table.
eg. There is a table, with numbered field in each record. Sometimes the number sequence is skipped,
(record 1 No field = 1, rec2 = 2, rec3 = 3, rec4 =5, rec5 = 7)
I want a method of making a list of those missing numbers, in this case 4 and 6, in their sequence. Would posting the missing number, if it didn't follow the previous, to a different table be a reasonable idea ?

Brian.

[Tom Cone Jr]

RE: Finding numbers not in a sequence

Brian, I can't think of a really easy way to do what you describe. Your approach will work if there is only one missing number in the gap between the current record and the previous record, and provided the table is sorted on the number field. Otherwise it will miss some, or so it seems to me.

A brute force approach would:

- sort the table on the number field
- fetch the first and fetch the last, and use them to create
an array with lower bound equal to the first value, and an upper bound equal to the last value
- fetch the first
- fetch each until you hit the last, examining the number field value each time and setting flags in the array elements that stand for missing numbers

Databases are wonderful things but some of the hardest things I've had to design are reports that list information that's missing from a sorted table. Identifying what's 'not there' is a lot more difficult than reporting on what's stored in the database.

-- tom

[JerryBrightbill]

RE: Finding numbers not in a sequence

Another brute force method is to create another table with just a field for the numeric values and then fill the new table with every possible value with no skipped numbers. Then subtract the first table which may have missing numbers from the table with all of the numbers. The subtract operation has a filter option, so you can set the filter to just check numbers equal to or smaller that the largest number in the first table. The result will have just the "missing" numbers.

If this is something that must be done frequently, the table with the numeric test field only has to be created once, as long as enough records are included to cover future possibilities. Since it is only one field, it probably won't take much space.

Jerry

[Edward F. Schulz]

RE: Finding numbers not in a sequence

It seems that adding a child table, one to many, and sorting the parent
on the no_field, then writing no_field_child when observing the parent
for previous value +1.
efs

[EYoo]

RE: Finding numbers not in a sequence

I have done a similar task where I had to identify the skipped sequence record and review each one to take certain corrective action. Fortunately, the table was not a large one.

I had added a temporary 1-character field in the table. Then sort the table by the sequence number field. Using an update operation, if the difference between the current record sequence number and previous record sequence number (previous() function) was greater than 1, I would update the temporary 1-character field with "X" Once the operation was completed, I review the table in browse to make correction. It was tedious to make correction but it worked. I hope this helps.

Eugene

[Robert Adler]

RE: Finding numbers not in a sequence

Brian:

I believe there is an article in LearnAlpha.com by Barry Rochford "Finding Missing Numbers within a Sequence"

It sounds like it may apply to your needs.

I hope this helps.

bob adler

[BDennis]

RE: Finding numbers not in a sequence

Thanks everbody for your inputs. I had this in A4, and used a search/sort to do more or less what has been suggested above. Just didn't want to miss any better way of doing it.

Thanks for the ideas and responses. Most useful.

Brian