We have a simple table with hundreds of entries in it. It is now a requirement that the "sample number" field contain a unique value. Can the definition of this table be changed to designate the "sample number" as a primary key? If so, what will happen to any existing duplicates?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Westi on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Modifying a Table to have a Primary Key 2
- Thread starter bepryor
- Start date
-
1
- #3
ProgramError
Programmer
You will have to copy the existing data into another table with a additional unique key field. Duplicates will no longer be of concern in the new table. If you did want to remove the duplications try a query with group by references. There are threads in the FAQ concerning duplication records.
Ian Mayor (UK)
Program Error
There's ALWAYS more than one way to skin a cat!
But only one way to get it RIGHT!
Ian Mayor (UK)
Program Error
There's ALWAYS more than one way to skin a cat!
But only one way to get it RIGHT!
ProgramError
Programmer
Maybe he wants to keep the duplicated records.
Alternatively querying and renumber the duplicate indexes will give him a unique key but this will reorder the table unless he has another field on which he can reference its order. Copying the table will retain the original order.
I respect you opinion Remou as you have helped me out countless times. I just made another suggestion based on the question.
Ian Mayor (UK)
Program Error
There's ALWAYS more than one way to skin a cat!
But only one way to get it RIGHT!
Alternatively querying and renumber the duplicate indexes will give him a unique key but this will reorder the table unless he has another field on which he can reference its order. Copying the table will retain the original order.
I respect you opinion Remou as you have helped me out countless times. I just made another suggestion based on the question.
Ian Mayor (UK)
Program Error
There's ALWAYS more than one way to skin a cat!
But only one way to get it RIGHT!
-
1
- #6
ProgramError, no need of a new table for your suggestion:
DoCmd.RunSQL "ALTER TABLE myTable ADD COLUMN myID COUNTER(1,1) CONSTRAINT PrimaryKey PRIMARY KEY;"
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
DoCmd.RunSQL "ALTER TABLE myTable ADD COLUMN myID COUNTER(1,1) CONSTRAINT PrimaryKey PRIMARY KEY;"
Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886
- Thread starter
- #8
- Status
Similar threads
- Question
- Locked
- Question