Checking if records are missing

[sacheson]

I have a table within my database with 17,000+ records.&nbsp;&nbsp;I did not design the database from the beginning, and the person who did did not specify a unique identifier for the primary key - the Primary Key is 'Indexed with Duplicates'.&nbsp;&nbsp;There are 4326 numbers - starting at one and going to 4326, and the nature of the data has most of these numbers duplicated.*<br><br>Some records appear to have been deleted somewhere along the way.&nbsp;&nbsp;I am looking for a way to search for a missing number so I might narrow the search for the missing data.&nbsp;&nbsp;Example: if parcel number 1254 is missing, I want to find a way to search and be alerted it is missing if 1253 and 1255 exist ... understand?<br><br>Any help is greatly appreciated.<br><br>* Each number is a parcel of land, each name entry is an owner (or part owner) of the parcel.&nbsp;&nbsp;If several people share ownership of some land, the parcel number is duplicated for each name.

 

[kathryn]

It's not pretty, but the only way I can think of is to use a recordset and step through the records.&nbsp;&nbsp;Your recordset should be based on a query which sorts by your ID.<br><br>Something like this.<br><br>***Begin Code<br><br>Sub MissingID()<br><br>dim rst as recordset<br>dim intID as integer<br><br>set rst = currentdb.openrecordset(&quot;YourSortedQueryName&quot<br><br>rst.movefirst<br>'get first ID number<br>intid = rst!id<br>'start stepping through query<br>do until rst.eof<br><br>&nbsp;&nbsp;rst.movenext<br>&nbsp;&nbsp;'compare the two IDs<br>&nbsp;&nbsp;'if the difference between them is <br>&nbsp;&nbsp;'greater than one, print the two<br>&nbsp;&nbsp;'ID numbers to the debug window<br>&nbsp;&nbsp;if rst!id-intID&gt;1 then <br>&nbsp;&nbsp;&nbsp;&nbsp;debug.print intid;rst!id<br>&nbsp;&nbsp;end if<br>&nbsp;&nbsp;'set intID to the current record<br>&nbsp;&nbsp;'ID before moving to the next record<br>&nbsp;&nbsp;intID = rst!id<br><br>loop<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>End Sub<br><br>***End Code***<br><br>This should get you started, assuming I understood your question correctly.&nbsp;&nbsp;Let us know if you need anything else.<br><br> <p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br>

 

[jimmythegeek]

Copy and paste the following function into a blank module:<br>Change the red areas appropriately:<br>Open the debug window (ctrl + G):<br>Type &quot;Call GetMissingNums&quot;<br>And watch<br><br>Now you can copy all the numbers into a text file or something to save them if you wish.<br><br>==========<br>Public Sub GetMissingNums()<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim db As Database<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim rs As Recordset<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim tempno As Integer, FileNo As Integer<br>&nbsp;&nbsp;&nbsp;&nbsp;Dim strSQL As String, strNumbers As String<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;strSQL = &quot;SELECT <font color=red>ParcelField</font> FROM <font color=red>YourTableHere</font> GROUP BY <font color=red>ParcelField</font> ORDER BY <font color=red>ParcelField</font>&quot;<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Set db = CurrentDb()<br>&nbsp;&nbsp;&nbsp;&nbsp;Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)<br>&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;With rs<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If .RecordCount &gt; 0 Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveFirst<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tempno = !<font color=red>ParcelField</font><br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Do Until .EOF<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If tempno + 1 &lt;&gt; !<font color=red>ParcelField</font> Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;If strNumbers = &quot;&quot; Then<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strNumbers = tempno + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;strNumbers = strNumbers & vbNewLine & tempno + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Else<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;.MoveNext<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;tempno = tempno + 1<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Loop<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;End If<br>&nbsp;&nbsp;&nbsp;&nbsp;End With<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;<br>&nbsp;&nbsp;&nbsp;&nbsp;Debug.Print strNumbers<br><br>&nbsp;&nbsp;&nbsp;&nbsp;rs.close<br>&nbsp;&nbsp;&nbsp;&nbsp;db.close<br>End Sub<br>========== <p>Jim Lunde<br><a href=mailto:compugeeks@hotmail.com>compugeeks@hotmail.com</a><br><a href=

http://www.cgeek.com>CompuGEEKS</a><br>Custom

Application Development