Finding Missing Numbers in Sequence 2

[matth]

I need a query that will find the missing numbers in a sequence. I have some SQL that a co-worker gave me, but Access doesn't like it.

The table I am pulling from does not sort the column that I am pulling the numbers from.

The column does not start with 1 (actually starts about 28000 or so).

The table itself is a list of entries where one of the fields in each record references a page number in a book. What I need to know is which page numbers are missing from our list (never been referenced).

Thanks in advance,

matth

 

[rac2]

" ... one of the fields in each record references a page number in a book."

Finding missing numbers can be time-consuming.

The problem might be approached this way. Make a table with one column. Fill the column with all of the page numbers for a book. I am thinking these might begin at 28001 and end at 28362 for the first book with 362 pages. Call this table AllPageNumbers and name the column page_number.

Assume the table you have already is named BookPagesReferenced with a column named page_number which is the number of the page which was referenced. As you say it is not really a page number since books don't have 28,000 pages, but it must map into the page numbers. You will use that same mapping to create rows in AllPageNumbers. Repeat this for all of the books.

The SQL to find the unpopular pages is

SELECT page_number
FROM AllPageNumbers
WHERE page_number NOT IN (
               SELECT page_number
               FROM BookPagesReferenced
               )

I am thinking that the page_number column somehow identifies the book. If not you could add a column to AllPageNumbers that identifies the book, call it book; and add a WHERE clause to the subquery to match up rows from the two tables for each book.

SELECT book,
       page_number
FROM AllPageNumbers
WHERE page_number NOT IN (
               SELECT page_number
               FROM BookPagesReferenced
               WHERE book = AllPageNumbers.book
               )

To make the rows in AllPageNumbers you could use Excel which has a feature for filling a column with sequential numbers. Import that column into Access. Possibly Access has such a feature to automatically generate rows.

 

[johnnymac43]

Not necessarily a fun thing to do, but worth knowing:

In Access 2000, try the following:

> Define a field in a table as Number, and remove the default value of 0 from the field properties.
> Enter a number in the field and press the down arrow.
> Enter the next consecutive number in the field and press the down arrow. The third consecutive number will appear in the field (magic).
> Hold down the down arrow for however long it takes to generate the numbers you want (big magic).

HTH

John

 

[MichaelRed]

A variation on the theme. Use an aggregate query to find the min and max values of the set (these could be within a range, assuming rac2's 'thinking is on target'). this can at least limit the search.

Create a table which hols only a 'PageNum' column (for a one shot, this can be a temp table, if it is a repetitive execise, it could be a permanant one).

Start with an 'empty' table. Either by crreating it or deleting all records. Do a simple loop to add records in the range.

Create an "unmatched" query between the tables. The Ms. A. query meny includes a simplistic 'wizzard' which will create the unmatched query for you.

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[danvlas]

John, your big magic took my breath away...really.

How did you find the trick???



Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[johnnymac43]

Daniel,

I found this behaviour quite by accident, as is probably the case with most great discoveries.

While using Access to convert some dBASE tables to SQLServer, I did something like the following:

> Put some stuff from a dBASE table into Access.
> Added an Autonumber field to generate unique keys for those records.
> Changed the Autonumber to Number (can't remember why)
> Ran a Make Table query to put some of the records into another table.
> Ran an Append query to add some more stuff to the new table.
> Added the key values manually, and made the big discovery.

I didn't investigate the reason why until much later. It turns out that a Make Table query doesn't give a Number field a default value, which I guess makes sense when you think about it.

Cheers

John

 

[raskew]

Interesting exercise!
Expanding a bit on Michael Red's suggestion--

Try exporting Northwind's Orders table and naming it Orders3.
Go into Orders3 and delete a few records at random.

Then copy/paste the following to a new module and run the FindMissingNumbers() function:

Private Declare Function a2Ku_apigettime Lib "winmm.dll" _
Alias "timeGetTime" () As Long
Dim lngstartingtime As Long

Sub a2kuStartClock()
    lngstartingtime = a2Ku_apigettime()
End Sub
Function a2kuEndClock()
    a2kuEndClock = a2Ku_apigettime() - lngstartingtime
End Function

Function FindMissingNumbers() As Boolean
Dim db As DATABASE
Dim rs As Recordset
Dim strSQL As String
Dim loend As Long, hiend As Long
Dim reccount As Long, recnum As Long
Dim rechold As Long
Dim NL, msg

NL = Chr(13) & Chr(10)  ' Define newline.

'Start the clock
a2kuStartClock

Set db = CurrentDb
strSQL = "SELECT Min(Orders3.OrderID) AS MinOfOrderID, Max(Orders3.OrderID) AS MaxOfOrderID, Count(Orders3.OrderID) AS CountOfOrderID, [maxoforderid]-[minoforderid]+1 AS Expr1" _
    & " FROM Orders3;"
Set rs = db.OpenRecordset(strSQL)
loend = rs!MinOfOrderID
hiend = rs!MaxOfOrderID
reccount = rs!CountOfOrderID
recnum = rs!Expr1
rechold = loend
strSQL = "SELECT Orders3.OrderID FROM Orders3 ORDER BY OrderID;"
Set rs = db.OpenRecordset(strSQL)
msg = "There are " & recnum - reccount & " missing numbers:" & NL & NL
Do While reccount < recnum
   If rs!OrderID <> rechold Then
      msg = msg & rechold & NL
      reccount = reccount + 1
      rechold = rechold + 1
   Else
      rechold = rechold + 1
      rs.MoveNext
   End If
Loop
rs.Close
db.Close
Set db = Nothing
'Stop the clock and display the results
msg = msg & NL & &quot;This procedure executed in: &quot; & a2kuEndClock & &quot; milliseconds&quot;
MsgBox msg, vbInformation + vbOKOnly, &quot;Find Missing OrderID's&quot;
FindMissingNumbers = True
End Function

 

[MichaelRed]

Actually, the above (by raskew) is somewhat different form the direction outllined in my earlier post. I had thought that the outline was sufficiently clear to implement and did not consider the necessity of posting a complete (code) soloution. Since this has (obviously) failed and [/b]raskew[/b] has done the majority of the real work, I provide a modification to his procedure more in keeping with the outline previously submitted.

I have made a few cosmetic changes, just to maintain soem personal style conventions (var names etc) and modified the dynamic SQL statements to accept the recordset and field names as argumnets instead of relying on the existance of specific table and field names within the procedure for the recordset for the list of "missing" values. The other 'significant' change is the creation of the (permanant) table tblSeqNums with the single field SeqNum which 'holds' the set of sequence values between the low and hi values found. This is, I believe' necessary for at least two reasons.

First, the user will probably want to do SOMETHING with or about the elements and the simple message box display provides little opportunity to work with or save the result. Athough the code is obviously intended as a simple illustration of a technique and it was predicated on a limited number of 'items' being returned, it is not even verifiable without some 'pencil work' to record what the procedure has returned.

Secondly, the message box can rapidly expand to an unmanageable size for a large number of missing items. It can actually easily expand to greater then the screen height, maing even the dismissal of the message box itself difficult.

The other area of change is, of course, the actual display of the results (the "UnMatched" query), as the replacement for the message box. I have included the SQL string for a SAMPLE of such a query generated by using the wizzard. It, of couse, refers to a table and field (tblIdNum.IdNumAsText which I generated just for the purpose of this exercise. It is easy to generate the sql string in code, or equally simple to simply use the wizzard to generate another query whenever desired, so the sql strin is JUST to illustrate the general syntax and is not necessary for the procedure,

Function basSeqGapsMR(MyRs As String, MyFld As String) As Boolean

    'Adapted from raskew's post to Tek-tips thread701-511412 _
     to find (and "Display") missing numbers from a sequence

    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset
    Dim strSQL As String
    Dim LoEnd As Long
    Dim HiEnd As Long
    Dim RecCount As Long
    Dim RecNum As Long
    Dim RecHold As Long
    Dim NL As String
    Dim MyMsg
    Dim Quo As String * 1
    Dim Idx As Long

    Quo = Chr(34)
    NL = Chr(13) & Chr(10)  ' Define newline.

    'Start the clock
    a2kuStartClock

    Set dbs = CurrentDb

    strSQL = "SELECT Min(" & MyRs & "." & MyFld & ") AS MyLo, " & _
                     "Max(" & MyRs & "." & MyFld & ") AS MyHi, " & _
                     "Count(" & MyRs & "." & MyFld & ") AS MyCnt, " & _
                     "[MyHi]-[MyLo]+1 AS MyNumRecs" & _
                     " FROM " & MyRs & ";"

    Set rst = dbs.OpenRecordset(strSQL)

    LoEnd = rst!MyLo
    HiEnd = rst!MyHi
    RecCount = rst!MyCnt
    RecNum = rst!MyNumRecs
    RecHold = LoEnd

    strSQL = "Delete * From tblSeqNums;"
    dbs.Execute strSQL

    Set rst = dbs.OpenRecordset("tblSeqNums", dbOpenDynaset)
    Idx = RecHold
    While Idx <= HiEnd

        With rst
            .AddNew
                !SeqNum = Idx
            .Update
        End With

        Idx = Idx + 1

    Wend

    'This is just the SQL statement for the Query to Display the Results, _
     the Missing values in the sequence
    strSQL = &quot;SELECT tblSeqNums.SeqNum &quot; & _
             &quot;FROM tblSeqNums &quot; & _
             &quot;LEFT JOIN tblIdNum ON tblSeqNums.SeqNum = tblIdNum.IdNumAsText &quot; & _
             &quot;WHERE (((tblIdNum.IdNumAsText) Is Null));&quot;

    'Stop the clock and display the results
    MyMsg = &quot;This procedure executed in: &quot; & a2kuEndClock & &quot; milliseconds&quot;
    MsgBox MyMsg, vbInformation + vbOKOnly, &quot;Find Missing OrderID's&quot;

    basSeqGapsMR = True

End Function

MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[NCBrit]

This is exactly what I am looking for, however I am having difficulty implementing Michael's code. Could you give me some basic pointers on how to make this work in a sample database.

Thanks.

 

[MichaelRed]

I. at least, am at more-or-less a total loss as to what (else) you might need. Please post some details of what you cannot 'get to work'. The only areas I can concieve that would give even a rank beginner any trouble might be the table / field / variable names.





MichaelRed
mlred@verizon.net

 

[JonFer]

NCBrit - do you know how to run the function with parameters?

John

http://www.interautomateconsulting.com

 

[misscrf]

I know this is late in the game, but since you all answered my question so well, I thought I would put my 2 cents in.

I need to find missing numbers in a sequence too. I know the start and end of the sequence, so I will use the magic to create my all numbers table.

To find the missing, I will just run the find unmatched query. Won't that find the ones in the all numbers that are not in my table, giving me my missing numbers?
no sql needed.
Thanks all!


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MichaelRed]

" ... I know this is late in the game ... "

If so, I missed it ... seems to happen more often lately ... Mayhap an age related syndrome?

AnyWhat, the simple answer is yes. The exhaustive answer is to advise you to read and understand the procedures posted above. The gist of it is simply to avoid the messy issue(s) of tracking down the details, such as what are those end points (to mention JUST one) by hand. Here, the process is encapsulated so you only need to know (or find out) the table (actually ANY recordset) and the field names of interest, and feed these to a single beastie. He (ye yon beastie) will chew up the fodder and 'spit' out the remains.

Some folks feel like it is way much of a bother to actually create these re-useable little procedures as 'THEY" can do the whole thinnggggyyyyyy faster by manually getting those uguly details and doing a bit of manual labor - ending up with the ~~~~ same results. From your inquiry, it looks-like you are of this mindset.

Others prefer to think the issue through, define an aproach, formalize a soloution, test is (at least a little) and pronounc the entire set of problems solved. Obviously, in stumblilng through this thread, you have 'met' at least a couple of this (other) perspective.

As in most endeavours, there are multiple possible and viable soloutions to this simple scenario. Wheather you choose one presented here or continue with your quest or follow the reasoning you stated is just a choice.

If, in your continuing persuit of soloutions, you find or devise one which offers some advantage to our efforts, I'm sure we would collectively appreciate the advantage of that knowledge, should you be so kind as to inform us thereof.






MichaelRed