Auto number in an existing table

[Nick]

I am converting a Approach96 database to Access 2007.
I imported the 3 tables with no problem and created my forms, etc.
now the problem. It is a "Customer Table", Property Table", and "Invoice
Table". I have a few thousand records in there.

I have a "properties" table that has a "property Number" field,
This field was a "next number" created in Approach. There are many missing
numbers from deleted records over the years.

This "property number" field is linked to the "property Number" field in the
Invoice table.

Now the problem, I can not use autonumber and have it pickup where the last
"proprty number" ended. Auto number must be set up as a new field/colum ?
correct?

So I added a "auto property number colum" to my Properties table, problem is
these numbers do not match the numbers that Approach put in the table, due to
many deleted records over the years.

So is there any way to have the "Invoice table" replace it's "property
number" value with the coresponding "auto property number" value from the
same record in the Propertiy Table??

Many Thanks,
Nick

 

[Douglas J. Steele]

Create a new table and set the property Number field as AutoNumber.

Write an Append query that takes your data from the existing table and adds
it to the new table you just created.

The next value assigned for property Number will be one greater than the
largest value already in the table.

 

[Tom Wickerath]

Hi Nick,

Now the problem, I can not use autonumber and have it pickup where the last
"proprty number" ended.

Actually, yes, you can. You can seed the autonumber. Here is a method that
uses a DDL (Data Definition Language) query to set the next number at 1000,
with an increment of 1:

Reference: Allen Browne's web site
http://allenbrowne.com/func-DDL.html#AdjustAutoNum

Function AdjustAutoNum()
'Purpose: Set the Seed of an AutoNum using DDL.
Dim strSql As String

strSql = "ALTER TABLE MyTable ALTER COLUMN ID COUNTER (1000,1);"
CurrentProject.Connection.Execute strSql
End Function


You can start with an empty table (compact the database first to reset the
autonumber) and then append existing records to this table using an append
query. As long as you do not try to add the same value twice, the append
operation should allow you to maintain the existing numeric values, including
the gaps from past record deletions.

So I added a "auto property number colum" to my Properties table, problem is
these numbers do not match the numbers that Approach put in the table, due to
many deleted records over the years.

If you want to add an autonumber column to a table that already has records,
the numbers will not match your existing values. However, you could add a new
Long Integer field to the child tables, and then run the appropriate Update
query to update the values in this new field from Null to whatever the
corresponding autonumber value in the parent table is. Such a query would
rely on a join between the existing key fields from the Approach database.
Once you were satisfied that the update query had run successfully, you could
then create a query that joins the new autonumber field to the new Long
Integer field, and run side-by-side queries to compare the results (one query
involving a join with existing fields from Approach).


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[KARL DEWEY]

Yes. BACKUP DATABASE BACKUP DATABASE
You need to add a new number field, long integer, field to second table. In
design view put both tables. Click on the old number field in the table that
you added the autonumber and drag to the other. Double click the connecting
line and select the option to see all records from table WITHOUT the
autonumber and only those that match from the autonumber table. Drag fields
down to the grid. Use criteria of Is Null for field of autonumber table to
run an unmatch query.
Fix all records that do not match.

Then change connecting line to show all match and change to update query.
Update long integer number field with the autonumber value.

Create a one-to-many relationship.

Best of luck.

 

[Nick]

Thanks Douglas,
That did the trick, very simple solution.
Thanks again for taking the time to respond

Nick