 | |
Changing Primary Key Value
comp.databases.ms-access comp.databases.ms-access
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Changing Primary Key Value -
09-02-2011
, 07:14 AM
#2
| |||
| |||
|
|
I got an acquired table in which Primary Key data type is text and the key is prefixed with letters DC followed by number strings, like DC520309391, DC650725, DC10002 etc. I have 300+ records in the table and would like to remove the prefixed DC from the Primary Key. How can I do this. Thank you in advance. Rafeek. |
Mid(MyField, 3, Len(MyField) - 2).
This is starting at the 3rd letter/number, i.e. after the DC, then using all
the numbers to the end. If MyField is a primary key, duplicates will not be
allowed, but I assume if there are no duplicates in the original table, there
won't be any in the modified table.
Dont forget the result will be a text field, not a numeric field.
make sure you have a backup of the original table before you start.
HTH
Phil
#3
| |||
| |||
|
|
On 02/09/2011 13:14:52, "rafeek" wrote: I got an acquired table in which Primary Key data type is text and the key is prefixed with letters DC followed by number strings, like DC520309391, DC650725, DC10002 etc. I have 300+ records in the table and would like to remove the prefixed DC from the Primary Key. How can I do this. Thank you in advance. Rafeek. Assuming the field Name is MyField, do an update query and set MyField to Mid(MyField, 3, Len(MyField) - 2). This is starting at the 3rd letter/number, i.e. after the DC, then using all the numbers to the end. If MyField is a primary key, duplicates will not be allowed, but I assume if there are no duplicates in the original table, there won't be any in the modified table. Dont forget the result will be a text field, not a numeric field. make sure you have a backup of the original table before you start. HTH Phil |
#4
| |||
| |||
|
|
I got an acquired table in which Primary Key data type is text and the key is prefixed with letters DC followed by number strings, like DC520309391, DC650725, DC10002 etc. I have 300+ records in the table and would like to remove the prefixed DC from the Primary Key. How can I do this. |
you will need to set the Cascade Updates on for that relationship.
Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
#5
| |||
| |||
|
|
On Fri, 2 Sep 2011 15:14:52 +0300, "rafeek" <rafeek (AT) mymail (DOT) com> wrote: I got an acquired table in which Primary Key data type is text and the key is prefixed with letters DC followed by number strings, like DC520309391, DC650725, DC10002 etc. I have 300+ records in the table and would like to remove the prefixed DC from the Primary Key. How can I do this. Are there any child tables with that field as a foreign key? If so you will need to set the Cascade Updates on for that relationship. Tony -- Tony Toews, Microsoft Access MVP Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/ For a convenient utility to keep your users FEs and other files updated see http://www.autofeupdater.com/ |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.