find missing numbers in a sequence

[zoonkai]

I am running access 97 and have a table that has sequences of document numbers...that have been scanned into the database. i would like to have it show me...1 of two ways....it just start reading in order and for instance if it found a 1 then it didnt' find a two it would print 2 to the report that it is missing....or if i said....find all missing numbers between 1 & 1000 and it just tell me the missing numbers....this would be a great help because i already have over 100,000 numbers to sort through. this is a text field though...because some numbers are like "0B1001" (minus the quotation marks)

Any help would be greatly appreciated!!!

Thanks

Donald Dixon

Don-Nan Pump & Supply Co.

Midland, TX

(915) 682-7742
Donald (Zoonkai) Dixon
donnan@don-nan.com

http://www.don-nan.com

 

[DougP]

The first step in figuring out what you don't have is seeing what you do have.
So create a query that gets the numbers you do have and put them in a temporary table. heres how
Open “Excel” Yes Excel trust me, and start in the first cell and key in the number of your starting point. Say its 1000 then click it and drag down as far as you need to get to the last number say 1100 so you have the first column highlighted down 100 cells.
Next click the “Edit” menu then “Fill” menu then “Series” if your numbers are 1 apart like 1000, 1001, 1002 just click the ‘OK” button. Now you have a list of all the supposed numbers. Right. Highlight the whole list of numbers and save it to the Windows Clipboard by pressing Ctrl-C.

Open your Access database and create a new table with just one field.
Call it the same field name as the other table.
Now click in the left next to the first cell on the little black arrow to highlight the first record. the first cell should be black. Press Ctrl-V to paste all of your numbers from Excel. It will say are you sure you want to paste xxxx records, click “yes”.

Close the new table.
Now create a new “Unmatched” query, add your regular table and the new one with all of the numbers just pasted from Excel. Link on the missing number field. Run your query and it will find all of the numbers not in both tables. Make a report from it or just print the query out.

DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive