none
Finding gaps in a set of numbers and fill with new data RRS feed

  • Question

  • I am trying to find gaps in a set of numbers (part numbers) in order to fill them with new data.

    Here is the query that isn't displaying the stop values as I had expected.  You will also find the table structure below.

    “qryGaps” Query

    SELECT start, stop

    FROM (select Format(Val(m.PartNumber) + 1,"000000") as start,

        (select Format(min(Val(PartNumber)) - 1,"000000") from tblParts as x where Val(x.PartNumber) > Val(m.PartNumber)) as stop

      from tblParts as m

        left outer join tblParts as r on Val(m.PartNumber) = Val(r.PartNumber) - 1

      where r.PartNumber is null)  AS x

    WHERE stop is not null and left(start,2) = "02";

    m and r were the names from the example I was working from.

    The problem I am having is that it’s not showing the “stop” value in the results and I’m not sure why. I also need to be able to expand this to fins a gap of a certain size. So If I wanted to add 7 new part numbers, I should be able to find the first gap of 7 consecutive numbers.

    Thursday, June 14, 2018 4:02 PM

All replies

  • Hi sms71,

    The reason for stop value not displayed is because that there is no such record could meet the condition of the min value of PartNumber -1 will great than Val(m.PartNumber).

    And for solution of this scenario, custom code is more recommend than the complex SQL. For example, here is the code for your reference to get started:

    Sub FindAvaibleNums()
    Set thisDB = CurrentDb
    Dim strSQL As String
    Dim lastNum As String
    Dim currentNum As String
    strSQL = "select * from tblParts order by val(PartNumber) "
    Dim rstNums As DAO.Recordset
    Set rstNums = thisDB.OpenRecordset(strSQL)
    
    lastNum = rstNums!PartNumber
    rstNums.MoveNext
    
    Do While Not rstNums.EOF
        currentNum = rstNums!PartNumber
        If currentNum - lastNum = 1 Then
            
        Else
             Debug.Print "From: " + Str(Val(lastNum) + 1) + " To:" + Str(Val(currentNum) - 1) + " count:" + Str(Val(currentNum) - Val(lastNum) - 1)
        End If
        lastNum = currentNum
        rstNums.MoveNext
    Loop
    End Sub

    Regards & Fei


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, June 15, 2018 10:36 AM
    Moderator
  • The query below is a solution which returns the start and end numbers of each range of missing numbers and the size of each range.  This solution uses an auxiliary Counters table with a single column Counter.  This can easily be populated with values from 1 to whatever maximum value will exceed the highest number you are ever likely to reach, either by some simple VBA in a loop, or by serially filling down a column in Excel and importing this into Access as a table.

    In this example the table in which the ranges of missing numbers are to be found is named Sequence and has a column on long integer number data type named SeqNumber.  Both the Counter column in Counters and the SeqNumber column in Sequence must be indexed uniquely, which is most easily done by making them the primary keys of the respective tables:

    SELECT NZ(EmptyRanges.StartOfRange,1) AS StartOfRange, EndOfRange,
    EndOfRange-StartOfRange+1 AS SizeOfRange
    FROM
        (SELECT DISTINCT
            (SELECT MAX(SeqNumber) +1
             FROM Sequence
             WHERE SeqNumber < Counters.Counter) AS StartOfRange,
            (SELECT MIN(SeqNumber) -1
             FROM Sequence
             WHERE SeqNumber > Counters.Counter) AS EndOfRange
        FROM Counters LEFT JOIN Sequence
        ON Counters.Counter = Sequence.SeqNumber
        WHERE SeqNumber IS NULL
        AND Counter <
             (SELECT MAX(SeqNumber)
              FROM Sequence)) AS EmptyRanges;

    Ken Sheridan, Stafford, England

    Friday, June 15, 2018 3:49 PM
  • PS:  The following might be more efficient:

    SELECT NZ(EmptyRanges.StartOfRange,1) AS StartOfRange, EndOfRange,
    EndOfRange-StartOfRange+1 AS SizeOfRange
    FROM (SELECT DISTINCT
            (SELECT MAX(SeqNumber) +1
             FROM Sequence
             WHERE SeqNumber < Counters.Counter) AS StartOfRange,
            (SELECT MIN(SeqNumber) -1
             FROM Sequence
             WHERE SeqNumber > Counters.Counter) AS EndOfRange
        FROM Counters
        WHERE NOT EXISTS
             (SELECT *
              FROM Sequence
              WHERE Sequence.SeqNumber = Counters.Counter)
        AND Counter <
            (SELECT MAX(SeqNumber)
             FROM Sequence))  AS EmptyRanges;


    Ken Sheridan, Stafford, England



    Friday, June 15, 2018 4:17 PM
  • Hello sms71,

    What's the state of the current thread? Does anyone's suggestion work for you? If it does, I would suggest you mark the helpful reply to close the thread. If not, please feel free to let us know your current issue.

    Best Regards,

    Terry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, June 18, 2018 8:02 AM