If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Find and replacing numerical values of varying lenth
I have a table of over 50k entries that are linked to various categories by
the category ID number. I need to re-map this entries to new category ID's the problem I have is that this database was set up using incremental ID numbers, so if i try to replace categoy ID 5 with 1641, then every instance of the number 5 is replaced, so you can imagine waht it does to 155 (116411641) so this is obviously going to bugger things up. Also these entries are linked to multiply categories, which are displayed comma delimited, i.e. 5,28,146,223. Is there a query I can write that will only replace the exact ID number I want and not every instance of that number inside any other ID number?? -- Bewildered of Bristol |
#2
|
|||
|
|||
Find and replacing numerical values of varying lenth
Presumably the category id is a field.
I would start by creating a table, say catfix: old_cat_id new_cat_id and then run an update query, something like: UPDATE catfix INNER JOIN Table1 ON catfix.old_cat_id = Table1.cat_id SET Table1.cat_id = [new_cat_id]; "Dave Barker" wrote in message ... I have a table of over 50k entries that are linked to various categories by the category ID number. I need to re-map this entries to new category ID's the problem I have is that this database was set up using incremental ID numbers, so if i try to replace categoy ID 5 with 1641, then every instance of the number 5 is replaced, so you can imagine waht it does to 155 (116411641) so this is obviously going to bugger things up. Also these entries are linked to multiply categories, which are displayed comma delimited, i.e. 5,28,146,223. Is there a query I can write that will only replace the exact ID number I want and not every instance of that number inside any other ID number?? -- Bewildered of Bristol |
#3
|
|||
|
|||
Find and replacing numerical values of varying lenth
David
The categories are stored in another table, but I do not wish to change thier id, just the id numbers that the entries are linked to, these are stored in one cell, comma delimited. I hope that makes sense?? -- Bewildered of Bristol "David F Cox" wrote: Presumably the category id is a field. I would start by creating a table, say catfix: old_cat_id new_cat_id and then run an update query, something like: UPDATE catfix INNER JOIN Table1 ON catfix.old_cat_id = Table1.cat_id SET Table1.cat_id = [new_cat_id]; "Dave Barker" wrote in message ... I have a table of over 50k entries that are linked to various categories by the category ID number. I need to re-map this entries to new category ID's the problem I have is that this database was set up using incremental ID numbers, so if i try to replace categoy ID 5 with 1641, then every instance of the number 5 is replaced, so you can imagine waht it does to 155 (116411641) so this is obviously going to bugger things up. Also these entries are linked to multiply categories, which are displayed comma delimited, i.e. 5,28,146,223. Is there a query I can write that will only replace the exact ID number I want and not every instance of that number inside any other ID number?? -- Bewildered of Bristol |
#4
|
|||
|
|||
Find and replacing numerical values of varying lenth
If the data is always entered with commas separating the items AND if there
are no spaces then you could use that to do your update. However, I would suggest that if you can you need to normalize this data by moving this repeating data into a separate table joined to the current table, so that you end up with one record for each category If you are using 2000 or later, then I would write a VBA function to handle this. That function ***might*** look something like the following UNTESTED code. Public Function ReplaceCat(strIn, strOldValue As String, strNewValue As String) Dim vCat As Variant Dim iLoop As Integer Dim strOut As String If Len(strIn & "") = 0 Then ReplaceCat = strIn Else vCat = Split(strIn, ",") For iLoop = LBound(vCat) To UBound(vCat) If Trim(vCat(iLoop)) = strOldValue Then strOut = strOut & strNewValue & "," Else strOut = strOut & vCat(iLoop) & "," End If Next iLoop strOut = Left(strOut, Len(strOut) - 1) ReplaceCat = strOut End If End Function "Dave Barker" wrote in message ... I have a table of over 50k entries that are linked to various categories by the category ID number. I need to re-map this entries to new category ID's the problem I have is that this database was set up using incremental ID numbers, so if i try to replace categoy ID 5 with 1641, then every instance of the number 5 is replaced, so you can imagine waht it does to 155 (116411641) so this is obviously going to bugger things up. Also these entries are linked to multiply categories, which are displayed comma delimited, i.e. 5,28,146,223. Is there a query I can write that will only replace the exact ID number I want and not every instance of that number inside any other ID number?? -- Bewildered of Bristol |
#5
|
|||
|
|||
Find and replacing numerical values of varying lenth
John
I'm using 2003, I'll give it a go though, cheers -- Bewildered of Bristol "John Spencer" wrote: If the data is always entered with commas separating the items AND if there are no spaces then you could use that to do your update. However, I would suggest that if you can you need to normalize this data by moving this repeating data into a separate table joined to the current table, so that you end up with one record for each category If you are using 2000 or later, then I would write a VBA function to handle this. That function ***might*** look something like the following UNTESTED code. Public Function ReplaceCat(strIn, strOldValue As String, strNewValue As String) Dim vCat As Variant Dim iLoop As Integer Dim strOut As String If Len(strIn & "") = 0 Then ReplaceCat = strIn Else vCat = Split(strIn, ",") For iLoop = LBound(vCat) To UBound(vCat) If Trim(vCat(iLoop)) = strOldValue Then strOut = strOut & strNewValue & "," Else strOut = strOut & vCat(iLoop) & "," End If Next iLoop strOut = Left(strOut, Len(strOut) - 1) ReplaceCat = strOut End If End Function "Dave Barker" wrote in message ... I have a table of over 50k entries that are linked to various categories by the category ID number. I need to re-map this entries to new category ID's the problem I have is that this database was set up using incremental ID numbers, so if i try to replace categoy ID 5 with 1641, then every instance of the number 5 is replaced, so you can imagine waht it does to 155 (116411641) so this is obviously going to bugger things up. Also these entries are linked to multiply categories, which are displayed comma delimited, i.e. 5,28,146,223. Is there a query I can write that will only replace the exact ID number I want and not every instance of that number inside any other ID number?? -- Bewildered of Bristol |
#6
|
|||
|
|||
Find and replacing numerical values of varying lenth
untested. Three passes, something like (sorry about all of the commas):
REPLACE("," & mystring & "," , ",5," , ",1641," ) IIF( LEFT(mystring,1) = "," , MID(mystring,2),mystring ) IIF (RIGHT(mystring,1) ="," , LEFT(mystring,LEN(mystring)-1) , mystring) my brain hurts "Dave Barker" wrote in message ... I have a table of over 50k entries that are linked to various categories by the category ID number. I need to re-map this entries to new category ID's the problem I have is that this database was set up using incremental ID numbers, so if i try to replace categoy ID 5 with 1641, then every instance of the number 5 is replaced, so you can imagine waht it does to 155 (116411641) so this is obviously going to bugger things up. Also these entries are linked to multiply categories, which are displayed comma delimited, i.e. 5,28,146,223. Is there a query I can write that will only replace the exact ID number I want and not every instance of that number inside any other ID number?? -- Bewildered of Bristol |
Thread Tools | |
Display Modes | |
|
|