Find Missing Numbers

[hstreff]

My boss had posted this same basic message so I am sorry for the repeat it actually may have not been in the right spot. I am very new to Access so please excuse my lack of knowledge and terminology. And the length of the message. I just want to try to explain as best I can.

I have a database with about 310,000 records. Each record was assigned a sequential number at one point (we were using dBase). The field is not an autonumber field it is actually a text field. Through the years various records have been deleted. We are trying to find a way to determine which seq_no's have been deleted. My seq_no (field name) is 7 characters. The numbers range from 0000000 to 0378801. I don't need to know how many records are missing, I need to know which seq_no's are missing. So if I have 1,3,5,7,9 I would like the result to return 2,4,6,8.

I have done some research here in the forum and through the help and some books we have purchased. I found a message here that I used as my starting point (http://www.access-programmers.co.uk/forums/showthread.php?t=99515&highlight=Find+missing+numbers). With the book I figured out how to create a module and I then put this code into a procedure. I somehow got it to run after making a few adjustments. But it was taking a very long time so I cancelled. I started looking at things more and then I changed integer to Long (not sure if that is correct). Then I started thinking that this may not be right at all because seq_no is actually a text field.

I am not sure if I am even on the right path. Any help or direction anyone could offer would be very much appreciated. I'm not even sure if I am doing this in the right place. When it does return an answer, where will it be, another table, or a display? I know that sounds stupid but I have not got that far yet. I am taking home the books again tonight to do some more reading.

Thanks in advance for any help or direction.

Heather

 

[spasticus]

hi.

im not that good with access myself but ill try to help.

try puttin this code (or similar) in the onlick event of a button or something. ... actually after starting to type it im wondering how efficient it will be .. thats if it works.

dim missing_seq_no as integer (not sure if this will do)

for i = 0 to 378801

missing_seq_no = DLookup ("*", "table_name", "[seq_no]=" & i)
list1.additem missing_seq_no 'this is just displaying the missing number - theres probably a better way of doing this

next i

 

[gemma-the-husky]

this may be quicker

create a table with a single field called id, type number

then have a module and put this code

Sub doit()
Dim x As Long

For x = 0 To 400000
    CurrentDb.Execute "insert into [COLOR="Red"]table1[/COLOR] ([COLOR="red"]id[/COLOR]) select " & x
    If x Mod 10000 = 0 Then
        MsgBox ("Processed " & x)
    End If
Next x

End Sub

this example will insert 400000 records into a table called table1, and a field called id, and will take about 5 minutes

then you can do a find unmatched with your table to see whats missing

i don't know if there's a quicker way to generate the check table

 

[hstreff]

The code that I posted, after the changes I made did work, kind of...I walked away and let it run. It returned a count of how many records were missing. It also listed a bunch of "0000000" with a square (space char??) in between each. So it seems to be on the right track just not totally.

So I will give this a try and let you know if/how it works. Actually just looking this over, it seems like a much easier route to take to get my answer.

Thanks for the input.

Heather

 

[gemma-the-husky]

the square is a non printable

might be a CR or a tab character (surely not a space) - would either make sense?

 

[hstreff]

No, I just thought it may be some kind of seperator...

I tried the code you provided. And it created the table, now I am just reading up on the unmatched query. Will it matter that my seq_no is padded with zero's? Will the unmatched query still work?

Can I create a field and replace it with the number with the leading zero. I found the FormatNumber command that has IncludeLeadingZero but I am not sure about the syntax??

Heather

 

[hstreff]

Almost there

OK...I figured out the leading zero. I made the field numeric for this purpose. I'll figure out how to convert back to text and repad with zeros later (inless you know an easy way). Now I ran the Find Unmatched Query and now I am stuck again.

It runs but it doesn't display anything. In dBase I would have wrote it like this - repl all ID_FOR with seqno->seq_no for ID = seqno->seq_no. This would have replaced the field ID_FOR with the seq_no from the Seqno database for all instances where ID and seq_no were equal. I just can't quite seem to get the syntax right for the criteria in the Query (I think that's the proper place).

If you could offer me any direction I'd appreciate it. Thank you so far for all your help.

Heather

 

[gemma-the-husky]

theres a find unmatched query wizard in access

but basically put both tables in a design window, and link them on the matching key (thin line)

now you will just see matches between the tables

dble - click the arrow and you you should get a join properties box (sometimes you get the queries properties and you dont want that) - you can now select to include just matches or all of one or the other table (i think these are left joins and right joins) - you should now get a bolder arrow going from one table to the other

now drop the id from both tables into the query design window, and in the criteria column put NULL, for id with the missing values

the query will bring up everything form the counter table that DOESNT HAVE an entry in the other table.

if you look at the SQL you can see how it is written.

hope this helps

once youve done it, the wizard will make sense

 

[hstreff]

Moving On...

Well. I don't know what I did the first time around, but this time, it was 1,2,3. I think I may have picked the wrong field to "display".

So now I have a table that has all my missing numbers. Is there a way to now take those numbers and summarize them so to say. So I can see where groups are missing? Like if 1-10 are missing I would like to see it that way also.

You have been a great help, thank you so much. I am sure I will get the hang of this eventually.

What about converting back to the text format? Do you have any direction on that part? When I convert back I am going to lose my leading zero's. Is there a simple replace command that will find the number of characters in the field and whichever have less than 7 char. pad the beginning with zero's?? Also where do I do something like this, the immediate window?? In dBase I have a command window to do things like this but the immediate window seems to be the closest to that, but I am still not sure, maybe it is in the SQL View?

Thanks again!

Heather

 

[raskew]

Hi -
Any chance you could create a table with just your sequence numbers, convert it to A97, zip it and post it? The reference you pointed to shouldn't take 5+ minutes. Would be happy to take a look.

Best wishes, Bob