Primary key field out of order

[DeepBlerg]

Hi,

I was given a database to fix up in MS Access 2002 which has a table with about 2500 records in it. I would like to reorder the entire table sorting the table alphabetically by the "Suburb" field and thus reassigning new ID number for primary key.

Anyone know how to do this?

Thanks.

 

[MissTipps]

I'm not sure why you feel you need to change the primary key field. Messing around with the primary key is probably going to cause you huge problems and destroy your relational integrity. Generally the order in which records are placed in a table is irrelevant. If you want to display them in a particular order, use a query. If the records are going to be queried by Suburb often, then create an index on that field, but I can't recommend strongly enough that you leave the primary key well alone.

Clare

 

[LittleSmudge]

For what it's worth I TOTALLY agree with MissTibbs.

In the Form's RecordSource, if it says "tblTableName" at the moment simply change it to

"SELECT tblTableName ORDER BY Suburb"

and the job is done.


Alternativly, in the table design change the Suburb field to Index ( Allow Duplicates ) and move it to the top of the table (by dragging it up in the design view) so that it is the primary sort index



G LS

 

[mikey69]

Another voice in agreement. It is not a good idea to use autonumbers for anything other than primary keys. And primary keys should emphatically not have other significance hung on them. they're just numbers and numbers are cheap in the world of Access. Add as many other fields to your record as you like and sort them, query them to your heart's content.