Have you ever wanted to customise an autonumber primary key in an Access table? For example if you delete some records from a table, the auto numbers originally used by the deleted records are not used again, so there are gaps in the primary key numbers. You might then want to reorganise the primary key with successive numbers. Or suppose you want to have a primary starting at a different number, for example 100, you might wonder how to do this. This articles shows you how to restore an autonumber list so there are no missing numbers, and also shows you how to make a custom autonumber list.

First we'll look at how to restore an autonumber list. This is best done before you develop queries, forms and reports or link your tables in relationships. If you have table links, these must first be deleted, before making the changes to the primary key, which involves deleting and then restoring the primary key.

Once the changes have been made, you can create the table links again, so be sure to note which ones you have temporarily removed. If you do try to rename or delete a primary key which is involved in a link to any other table, Access will stop you from making these changes.

If you have queries, forms or reports based on the table some fields may not display correctly until you complete the autonumber restore. Once completed you then need to recreate the table links. Any queries, forms or reports based on the table will display correctly as the restored primary key will have the same name as the original.

Suppose you have an Access table with three headings, Ref, Firstname and Surname. The Ref field is the primary key with data type is auto number, and the Firstname and Surname fields are text data types. You then add, say, five records and display the results. All names will be listed with the numbers 1 to 5 showing in the Ref column. If you then delete record number 3 you'll find that the autonumber 3 is not used in the table again and all records keep their original number. Any new records carry on from where the original number sequence.

To restore the auto numbers we're going to remove the original primary key, then add it back in again, as Access will not allow you to change a primary key's properties. So open the table in design view, then delete the primary key, the Ref autonumber field. Click Yes on the Access warning prompts to delete the field. Then add a new field in the same position with the same name Ref, select the autonumber data type and set it to be the primary key, then save your design changes.

Now if you switch to datasheet view you'll see the restored autonumber list with no breaks. Just be aware that some records will have been renumbered in this process, so it's always best to make this kind of change before creating other Access items which are controlled by the table. You then close the table and relink table relationships you may have earlier unlinked. Provided that you have given the new primary key the same name as the original, all queries, forms and reports will work as before. And there you have it - a restored primary key.

To customise a primary key to start from a different value than 1 or to include text as well as a number, we need to create a new query based on all the fields in the original table. When you create the new query you select the table in the Show Table panel and click Add, and close the panel. Then where the table shows in the display area you double click on its blue heading. All the table fields will become blue. Then drag any of these blue fields to the top cell in the left hand column in the lower part of the display. Now all the table fields will show in the lower query builder. All we need to do is add a new custom field in this lower part. We're going to add a new column right of the column containing the Firstname field.

To do this click once into any cell in the column under this Firstname field. In the upper menu bar choose Insert, Column. A new column will appear to the right of the Firstname column. In the top cell of this new column type the following Ref2:Ref+100 and then run the query. You should see the new Ref2 column with the autonumbers starting at 100. Return to query design view. Now edit this to the following Ref2:"AA" & Ref +100 and run the query again. You should now see the new Ref2 column with the auto numbers starting with AA100. For neatness you may want to hide the original Ref column when the query runs. So return to query design view, and untick the show checkbox in the Ref column. You can then save the query with an appropriate name.

Now if you use this query rather than the original table to create forms, reports and other queries, and the primary key will now show your customised version. Don't forget to keep the original Ref field in the original table as its now being used in these other items - but you don't need to display it.

Attending a training course can be an enjoyable and informative way to develop your Access skills further, and there are many to choose from.