Replace with new primary key...

[Stanley.V]

Hi Guys,

I'm fairly new to the MS Access and know so little about it atm...

I have 2 tables: workorders and designs. Design table have collumn Design_ID(text) set as primary key and table workorder have Design_ID1,Design_ID2,Design_ID3 columns related to it...

I want to add Autonumber for the designs table as primary key and keep the Design_ID column as name of the design

The question is how do I replace all the actual design names in workorder table with corresponding new primary key number for all the names in Design_ID1,Design_ID2,Design_ID3

Thank you so much in advance!

Stanley:banghead:

 

[Ranman256]

in ALL tables , rename the [Design_ID] to [OLD_Design_ID] (a backup)
save.
make a new field in the master, [Design_ID]as autonumber,
in ALL other tables related, make [Design_ID] as LONG
now make an UPDATE query to match the OLD_id on both table so you can assign NEW ID to the child table , from the master table where master.OLD_id = child.OLD_id

 

[James Deckert]

you'll need to create the autonumber field for design table first.
Also create a new field in workorders Number(Long)
Then create an update query which will copy the autonumber from design into the new field in workorders. you can then delete the old field name in workorders if you wish.
If you've enforced referential integrity, you'll need to break the link and recreate it on the new fields.

 

[Stanley.V]

in ALL tables , rename the [Design_ID] to [OLD_Design_ID] (a backup)
save.
make a new field in the master, [Design_ID]as autonumber,
in ALL other tables related, make [Design_ID] as LONG
now make an UPDATE query to match the OLD_id on both table so you can assign NEW ID to the child table , from the master table where master.OLD_id = child.OLD_id

Could you guys guide me on how to make update query for this or maybe give me a link to some tutorial...much appreciated...

I.E I need to find "desing_name_bla_bla_bla" in workorders and then replace it to corresponding new primary key for "desing_name_bla_bla_bla" in designs...

Sorry for being so green