3

So: I have a small table which looks like :

PartNumber  ReplacedNumber
408077-5102 408077-5102S
408077-0102 408077-5102

As you can see here, i want to replace the Part number with a Replaced number, with the issue being if a is replaced by b, and b replaced by c, then it implies that a is replaced by c.

Anyone knowing a way to do this?

Thanks for your help

UPDATE:
Okay, here is the sample data: 

id PartNumber   ReplacedNumber
1  408077-5102  408077-5102S
2  408077-0102  408077-5102

As you can see, the value in the 2nd row is replaced (a is replaced by b, which in this case 408077-0102 is replaced by 408077-5102), and then b is replaced by c (408077-5102 is replaced by 408077-5102S in the 1st row).

That implies a = c. I want to avoid replacing values in repetition. I hope it is clearer now.

Thanks

Kristia Prokopiou
  • 39
  • 4
  • please post your question even more descriptive.tel your desired output clearly. – Saasu Ganesan Oct 19 '12 at 10:24
  • What is a,b and c and which column it is located? Can you provide sample data and your desired result set? – Zo Has Oct 19 '12 at 10:26
  • Okay, here is the sample data: id PartNumber ReplacedNumber 1 408077-5102 408077-5102S 2 408077-0102 408077-5102 As you can see, the value in the 2nd row is replaced ( a is replaced by b, which in this case 408077-0102 is replaced by 408077-5102 ), and then b is replaced by c. (408077-5102 is replaced by 408077-5102S in the 1st row. That implies a = c. I want to avoid replacing values in repetition. I hope it is clearer now. Thanks – Kristia Prokopiou Oct 19 '12 at 10:40
  • you just tell what is the output you need – SRIRAM Oct 19 '12 at 10:46
  • I guess it could be done in several ways. 2 rows will result in 1, so what I need is to merge the 2 rows into 1. I hope you understood the problem. – Kristia Prokopiou Oct 19 '12 at 10:51
  • 1
    @KristiaProkopiou How do you decide which value is replaced by which? How 408077-5102 is converted into 408077-5102S and how 408077-0102 is converted into 408077-5102? What is the criteria? Paste your table structure also please – Ertunç Oct 19 '12 at 10:51
  • Okay, so the data is fixed. its given, the numbers are not automatically replaced by another number. What I want to achieve is just the checking of the number that was replaced not to be replaced twice. Step 1: 40877-0102 is replaced by 408077-5102 as shown on the table. Step 2: 40877-5102 is replaced AGAIN by 40877-5012S. Conclusion: I want to avoid this. So how to I delete the unecessary row so the repetition is not shown in the resulting table? I hope this time I'm clear. Thanks – Kristia Prokopiou Oct 19 '12 at 10:56
  • @KristiaProkopiou It's still not clear enough, you need to address **Ertunc's** question, referring to A,B,C in this instance means nothing, what is your logic behind how the numbers are replaced and what they are replaced with? – Matt Donnan Oct 19 '12 at 12:40
  • Please see the update for a,b and c. The numbers replaced are given. What I want to do is avoid replacing twice. That necessarily means that it will result to 1 row, instead of 2. – Kristia Prokopiou Oct 21 '12 at 09:06

2 Answers2

1

If a is replaced by b, and b is replaced by c, then it implies that a is replaced by c could be written as this:

SELECT
  Replace.ID,
  Replace.PartNumber,
  IIF(Not IsNull([Replace_1].[ReplacedNumber]),
      [Replace_1].[ReplacedNumber],
      [Replace].[ReplacedNumber])
FROM
  Replace LEFT JOIN Replace AS Replace_1
    ON Replace.ReplacedNumber = Replace_1.PartNumber

(it's a SELECT query but it could be easily transformed in a UPDATE query) but this does not solve the case of c replaced by d, that (i suppose) also implies that a is replaced by d.

I think the only solution here is to use a recursive function:

Function searchReplaced(ReplacedNumber) as Variant
  Dim Look As Variant

  Look = DLookup("ReplacedNumber",
                 "Replace",
                 "PartNumber=""" & ReplacedNumber & """")

  If IsNull(Look) Then
    searchReplaced = ReplacedNumber
  Else
    searchReplaced = searchReplaced(Look)
  End If

End Function

And then you just launch this UPDATE query:

UPDATE Replace
SET Replace.ReplacedNumber = searchReplace([Replace].[ReplacedNumber])
fthiella
  • 48,073
  • 15
  • 90
  • 106
0

If that table has primary id, you can just update the table

update table set partnumber=replacednumber where id=id of the column you want to replace

id refers to primary key

SRIRAM
  • 1,888
  • 2
  • 17
  • 17
  • Okay, here is the sample data: id PartNumber ReplacedNumber 1 408077-5102 408077-5102S 2 408077-0102 408077-5102 As you can see, the value in the 2nd row is replaced ( a is replaced by b, which in this case 408077-0102 is replaced by 408077-5102 ), and then b is replaced by c. (408077-5102 is replaced by 408077-5102S in the 1st row. That implies a = c. I want to avoid replacing values in repetition. I hope it is clearer now. Thanks – Kristia Prokopiou Oct 19 '12 at 10:41
  • 1
    @KristiaProkopiou: please **do not** put code samples or sample data into comments - since you cannot format it, it's **extremely hard** to read it.... Instead: **update** your question by editing it to provide that additional information! Thank you. – marc_s Oct 19 '12 at 10:55
  • I apologise. I'm new, I just signed up so give me some time to learn how to use the site. Thanks for your patience – Kristia Prokopiou Oct 19 '12 at 10:58