sequential number help

[Lapchien]

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..?)

[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..?)

[Lapchien]

(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..?)

[Lapchien]

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..?)

[Pieter Linden]

"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?

[Kinetic - Paul G]

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..?)