By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,347 Members | 2,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,347 IT Pros & Developers. It's quick & easy.

sequential number help

P: n/a
I have list of numbers in a table (originally from autonumber in a different
database) from 1 to 1,000,000. The list is not in sequential order - there
are loads of numbers missing. How can I identify what numbers are missing?

Thanks,
Lap
(I'd like to then use this 'missing number list' to use for new records,
instead of autonumber - I think I need to use DMax - can someone summarise
it's use..?)
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
DFS
Lapchien,

If you're using this number for a primary key of some kind, you should
probably save yourself the trouble of "filling in the gaps" and just start
with the next available number. In your scenario, you'll have to identify
the missing numbers, store them somewhere, then use them one at a time and
mark each one as used (or delete it) until they're all gone. Almost
certainly not worth the trouble.

If you're concerned about having sequential ID numbers and your database
isn't very complicated, you could copy your data into a new table with a
fresh AutoNumber field.

But, if you are determined to find the missing numbers, try this:

dim i as long, rs as recordset, db as database
set db = Currentdb()
for i = 1 to dmax("ID","Table")
set rs = db.openRecordset("SELECT ID FROM Table WHERE ID = " & i & ";")
if rs.recordcount = 0 then
db.execute("INSERT INTO MissingIDs (ID) VALUE (" & i & ");")
endif
rs.close
next i


"Lapchien" <cc****@nospamplease.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
I have list of numbers in a table (originally from autonumber in a different database) from 1 to 1,000,000. The list is not in sequential order - there are loads of numbers missing. How can I identify what numbers are missing?
Thanks,
Lap
(I'd like to then use this 'missing number list' to use for new records,
instead of autonumber - I think I need to use DMax - can someone summarise
it's use..?)

Nov 12 '05 #2

P: n/a
(I guess Dmax wont work as it increments the number by 1 each time, and does
not reference a number table...)

"Lapchien" <cc****@nospamplease.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
I have list of numbers in a table (originally from autonumber in a different database) from 1 to 1,000,000. The list is not in sequential order - there are loads of numbers missing. How can I identify what numbers are missing?
Thanks,
Lap
(I'd like to then use this 'missing number list' to use for new records,
instead of autonumber - I think I need to use DMax - can someone summarise
it's use..?)


Nov 12 '05 #3

P: n/a
Thanks DFS
--
Thanks,
Chris
cc****@NOSPAMeclipse.co.uk

"DFS" <no******@nospam.com> wrote in message
news:vt************@corp.supernews.com...
Lapchien,

If you're using this number for a primary key of some kind, you should
probably save yourself the trouble of "filling in the gaps" and just start
with the next available number. In your scenario, you'll have to identify
the missing numbers, store them somewhere, then use them one at a time and
mark each one as used (or delete it) until they're all gone. Almost
certainly not worth the trouble.

If you're concerned about having sequential ID numbers and your database
isn't very complicated, you could copy your data into a new table with a
fresh AutoNumber field.

But, if you are determined to find the missing numbers, try this:

dim i as long, rs as recordset, db as database
set db = Currentdb()
for i = 1 to dmax("ID","Table")
set rs = db.openRecordset("SELECT ID FROM Table WHERE ID = " & i & ";")
if rs.recordcount = 0 then
db.execute("INSERT INTO MissingIDs (ID) VALUE (" & i & ");")
endif
rs.close
next i


"Lapchien" <cc****@nospamplease.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
I have list of numbers in a table (originally from autonumber in a

different
database) from 1 to 1,000,000. The list is not in sequential order -

there
are loads of numbers missing. How can I identify what numbers are

missing?

Thanks,
Lap
(I'd like to then use this 'missing number list' to use for new records,
instead of autonumber - I think I need to use DMax - can someone summarise it's use..?)


Nov 12 '05 #4

P: n/a
"Lapchien" <cc****@nospamplease.eclipse.co.uk> wrote in message news:<10***************@ananke.eclipse.net.uk>...
I have list of numbers in a table (originally from autonumber in a different
database) from 1 to 1,000,000. The list is not in sequential order - there
are loads of numbers missing. How can I identify what numbers are missing?

Thanks,
Lap
(I'd like to then use this 'missing number list' to use for new records,
instead of autonumber - I think I need to use DMax - can someone summarise
it's use..?)


Autonumbers are supposed to be meaningless - what's important is that
they're unique. is there a reason that you care what the numbers are?
If you *really* wanted, you could create a long integer field in your
table and then update that with incremental values... but why?
Nov 12 '05 #5

P: n/a
hi
create another table which has 1 field . eg chknumber and fill it with a
list of numbers from 1 to 1,000,000
use excel to create a list and then queries to load these in . . or what
ever method you find convenient.

when the list is complete, create a query that joins your new complete list
to the incomplete one.
set the link properties to show all new list records,

in the fields list add the new number 'chknumber' and the original number.
add a criteria under the original number of "not null"

this will show all the missing numbers. save this query and then use the
dmin function to find the lowest number you can use to create a new record
in your original table . . . hope this makes sense . .
cheers
paul g

"Lapchien" <no**@m.com> wrote in message
news:10***************@ananke.eclipse.net.uk...
(I guess Dmax wont work as it increments the number by 1 each time, and does not reference a number table...)

"Lapchien" <cc****@nospamplease.eclipse.co.uk> wrote in message
news:10***************@ananke.eclipse.net.uk...
I have list of numbers in a table (originally from autonumber in a

different
database) from 1 to 1,000,000. The list is not in sequential order -

there
are loads of numbers missing. How can I identify what numbers are

missing?

Thanks,
Lap
(I'd like to then use this 'missing number list' to use for new records,
instead of autonumber - I think I need to use DMax - can someone summarise it's use..?)


Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.