Autonumber format for existing records (1 Viewer)

[sha7jpm]

apologies,

did not explain myself fully in the earlier posting.

situation is as follows.

I have records that used to be recorded on a spreadsheet, they are all now in a database. the job number field has now been added.

I want this number to continue from where the last job finished which is number 2160.

therefore, at the moment projects are sitting on the database with no autonumber and the idea being that once they are acitve they are assigned the next autonumber in the job field.

I tried autonumber but that only seems to work for new records.

ideally the syntax would be something like,
if completed code =1 (means project is now active) then job number is 'autonumber'

so when the next project is turned to completed code 1 the autonumber puts in 2161 into the job number field.

hope I have confused you all....

cheers

John

 

[Pat Hartman]

I think that the job number should be assigned when the job is added to the table. If you do that, an autonumber will work just fine. Every row needs a unique identifier. It may as well be the job number.

If you can live with that. Create a new empty table with the job number being defined as an autonumber. You'll need to load the data in two parts. The first part will be all the records that already have job numbers assigned. the second will load all the rows with null job numbers. You'll need two queries. Query1 should select all columns from the source table including the job number and append the rows to the new table. The selection criteria should be Not Null for the JobNumber column. Query2 should select all columns EXCEPT the JobNumber and append them to the new table. The criteria should be - Null - in the JobNumber column.