Update table to add new records

[Russ]

I've got a dummy table that has two fields and no records. An
autonumber field and a field that I would like to hold a specific
sequential number. If no record in that field holds a number in the
range of 6294351 to 6296000 I would like the query to add one to fit
that range until all those numbers are used up. Should end up with
1649 records with sequential numbers in that range. Can I do this
with an update query?

 

[John Spencer]

An easy way to accomplish this would be to have the field indexed with no
duplicates. And another table (10 records) with one field with the digits 0 to 9.

Generic Solution to handle numbers from zero to 9,999,999
INSERT INTO YourTable (SequentialNumber)
SELECT N + N10*10 + N100*100 + N1000*1000 + N10000*10000 + N100000*100000 +
N1000000*1000000
FROM NumberTable as N1, NumberTable as N10, NumberTable As N100, NumberTable
as N1000, NumberTable as N10000, NumberTable as N100000, NumberTable As N1000000
WHERE N + N10*10 + N100*100 + N1000*1000 + N10000*10000 + N100000*100000 +
N1000000*1000000 Between 6294351 and 6296000


You could make that more efficient for the specific incident by using
something like the following.

INSERT INTO YourTable (SequentialNumber)
SELECT 6294351 + N + N10*10 + N100*100 + N1000*1000
FROM NumberTable as N1, NumberTable as N10, NumberTable As N100, NumberTable
as N1000
WHERE 6294351 + N + N10*10 + N100*100 + N1000*1000 <=6296000

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Russ]

Is your solution(s) a query? I am not too familiar with INSERT INTO.

 

[John Spencer]

Yes that is an APPEND query. It will add records to your table.

IF you want to see what records it would add you would remove the INSERT INTO
line and just run this as a select query.

If you have the unique index and you have some records in the range, then you
will get a message that x records were added and x records were not added due
to ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County