Missing Numbers

[amerifax]

I have a sequencial number in my data base. It counts 1,2,3 all the way to 302344. I want to find missing records. How would I come up with the missing records in a separte database. In other words if I had 1,2,4,5,8,9. I would want to have a database that would show me 3,6,7 since they are missing.

Bob

 

[RuralGuy]

The easiest way I know would be to use RecordSets and walk through it checking for the missing numbers. Then do an AddNew on the second table when you locate a missing number.

 

[The_Doc_Man]

I'm with RG here. You'll need VBA to do this right.

Overview: Write a function in a general module. You'll need a function because I'm going to suggest running the code via a MACRO RunCode action.

Top of code: Open a recordset to a table where you are going to identify the missing numbers.

Start a loop from 1 to highest number you wanted to see.

Do a DCount of records having the loop's index as the value you are seeking.

For every time the DCount returns 0, do an .Addnew, store the count, and do an .Update on the recordset you have open.

At the end of the loop, close the recordset and exit the function. It almost does not matter what you return as a function value. Perhaps the count of missing numbers, not that it matters that much. The recordset holds your results.

 

[KeithG]

I think you could actually do this with the DMin Function in an SQL statement. I will post an example if I have time.

 

[KeithG]

Look at the query in the below example it will show where there is a gap in the numbering of an autonumber field. Let me know what you think.


I know how you don't like to download attachments Doc Mann so below is the SQL in the example. There is also a table named tblName with fileds of ID and Name.

SELECT tblName.Name, tblName.ID, DMin("ID","tblName","ID>" & [ID]) AS NextSequentialNumber, ([ID]+1) AS RecordMissing
FROM tblName
WHERE (((DMin("ID","tblName","ID>" & [ID]))<>([ID]+1)))

 

[RuralGuy]

Outstanding Keith! Boy do I have a lot to learn about SQL.

 

[KeithG]

Here is another example. In my first it wouldnt include records if there were more than one in a row missing.

 

[amerifax]

Thank you so much for your help. I am in the process of transerring to Access from dBase...So unfortunately I am not very familiar yet. In dbase I would use (my table) and set the index accordingly. Would you be able to guide me on where I would set this up or how I would run this code that you have provided? Just so that I have a starting point. Thank you very much for all help I really appreciate it.

Bob

 

[hstreff]

What if??

I was looking over the solution you guys came up with. (Thank you very much for your help!) I just have 1 question. Will this still work if the field is not an autonumber field. The field is called seqno. It is a sequential number that we assign to records inhouse. In dBase right now we run a small program that takes the last number in the database and adds 1 to it until all the records are numbered. Will this make a difference??

Thanks again,

Heather

 

[amerifax]

>>KeithG - If my post has helped you solve your problems please add to my reputation. Click the scale in the top right side of this post.<<

Checking some of my previous topics. I'm definitely satisfied with the help I get on this forum.

I do not see a scale at the upper right side. Would it now be the thumbs up "Thanks" in the lower right?

Bob