Report only consecutive numbers

[George Too]

I have read a couple of posts that are close to what I need but not exactly.
I have a table that contains press number and roll number. There are 7 presses. Each press might have rolls numbered 1 to 100, but not always, sometimes some numbers are skipped.

Ex.
Press 1 rolls 1, 2, 4, 8, 9, 10, 23..
Press 2 rolls 3, 4, 5, 6, 9, 11, 15, ...

I need to extract only blocks of consecutive numbers for each press.

ex.
Press 1 rolls 1, 2, 8, 9, 10
Press 2 rolls 3, 4, 5, 6

Is this doable? Can a query do this or SQL or do I need code for it?

Thanks,
George Too

 

[dcx693]

How is the data stored in your database? This is doable, but the degree of difficulty will directly relate to how the data is stored.

 

[The_Doc_Man]

I once did something similar. I built a checking-account tracker DB that required knowledge of consecutive check numbers so that it could find missing checks. I needed code to do this. A query couldn't really do it quite right. The code was easier.

What I did was to build a little Public function (in a general module) that did this:

1. Opened a recordset holding the checks. The checknumber field wasn't the primary key (because deposits have no numbers, nor do interest postings, nor do check printing charges and adjustments due to errors or fees) so I built a query that selected checks only and sorted by check number. I opened that query as my recordset.

2. Read the first record. Got the first recorded check number. Stored it thrice: Once for "first in sequence," once for "next expected," and once for "last in sequence." Opened a second recordset directly to a DISCREPANCIES table. It showed first and last number, number missing, and a comment. Comment for the first entry: FIRST CHECK. The first and last number was the same as the first check number. Number missing was zero.

3. Did a loop on the recordset looking for the end of the recordset.

4. Within the loop, every time I stepped to a new record, I added one to "next expected," then compared the actual check number to the expected check number.

4.1 If numbers matched, updated "last in sequence" and continued the loop with no other action.

4.2 If numbers were not matched, appended a new record to the discrepancies list showing first in sequence, last in sequence, and (doing a little math on the actual number vs. the last known number), the size of the missing part of the sequence. (This was usually 1 after a VOID check except one month I had mislaid the checks and didn't post them until I realized there was a gap.)

Anyway, once all that was done, I updated the first in sequence and last in sequences and next expected to match the new check number (as though I had started over again.) In the new discrepancy record I included a comment: CHECKS MISSING. The first and last number were as stored. The number missing was as computed given the last valid number vs. the actual number.

5. At the end of the code I wrote one last record showing the first in sequence and last in sequence plus the comment: LAST CHECK. The first and last number were as stored. The number missing was zero. This record showed me the last good sequence, the one that ended the recordset.

Then I triggered this code from a macro that included a RunCode on the function I built. Could have done it from a form but didn't really want to do so.

Once this ran, the discrepancy table showed me good sequences including how long they were AND the size of the gap that broke the good sequence. Because it is important to realize that every record reported a good sequence AND the gap that broke the sequence. I didn't have records for good sequences and separate records for gaps.

Perhaps this is close enough to what you wanted that it will help you design what you needed.

 

[George Too]

Thanks all for your replies,

I was looking for something less complicated than what The_Doc_Man proposes, though I can give it a try. As for dcx693's question, each record contains, among other fields, the press and roll number. Each roll increment is in a separe record with the appropriate press number.

Here is an example:

Press-------Roll
1-----------2
4-----------7
4-----------8
1-----------3
5-----------9

Hope this further clarifies...

Thanks,
George Too

 

[dcx693]

That structure is good. What I would propose is a code solution. I think it might be reasonable to do this with queries, but it would be harder.

I would use a loop with a loop.

1. Open the table, sorted by press and roll numbers.
2. Go through each press, look for consecutive roll numbers.
3. Toss out the non-consecutive ones, write the consecutive results to a temporary table.

 

[George Too]

Will try them out and post my finds.
Thanks all for your help.

George Too