1

I have following Table "Replaced" in MS Access.

ID  PartNumber  ReplacedNumber  Status
4   b                       Active
5   c           b           Replaced
6   d           b           Replaced
7   e           c           Replaced
8   h           d           Replaced
9   104308          408077-0102 Replaced
10  310224          408077-0102 Replaced
11  0R5888          408077-0102 Replaced
12  4N6515          408077-0102 Replaced
13  4N6860          408077-0102 Replaced
14  408077-0102 408077-5102 Replaced
15  408077-5102 408077-5102S    Replaced
16  408077-5102S                Active

Query requirement

The final answer for any of these numbers (104308, 310224, 0R5888, 4N6515, 4N6860, 408077-0102, 408077-5102, 408077-5102S) should be 408077-5102S as this number has superseded all the rest. So, if my query has a criteria, [Please Enter part number to find the latest number], the resultant number should be 408077-5102S. As, it is the only Active number and has superseded all the rest.

Similarly, The final answer for any of these numbers (b, c, d, e, h) should be b as this number has superseded all the rest. So, if my query has a criteria, [Please Enter part number to find the latest number], the resultant number should be b. As, it is the only Active number and has superseded all the rest.

Any ideas? I have also looked at Replacing Values in SQL (Microsoft Access) but it does not help.

Community
  • 1
  • 1
user1765689
  • 11
  • 2
  • 2
    Have a look at http://stackoverflow.com/questions/763016/is-it-possible-to-create-recursive-query-in-access – Laurence Oct 22 '12 at 14:57
  • Also, have a look at this question: [What is the most efficient/elegant way to parse a flat table into a tree?](http://stackoverflow.com/questions/192220/what-is-the-most-efficient-elegant-way-to-parse-a-flat-table-into-a-tree) – mwolfe02 Oct 22 '12 at 15:33

1 Answers1

0

I think that the function searchReplaced in Replacing Values in SQL (Microsoft Access) is exactly what you need, why you say that it doesn't help?

Sub Test()
  Dim partToSearch, partReplace

  partToSearch = InputBox( "Part to Search? ")
  partReplace = searchReplaced( partToSearch )

  If partToSearch = partReplace Then
    MsgBox "Part " & partToSearch & " Active"
  Else
    MsgBox "Part " & partToSearch & " Replaced by " & partReplace
  End If
End Sub

Is the status column already present, or do you have to populate it?

Community
  • 1
  • 1
fthiella
  • 48,073
  • 15
  • 90
  • 106
  • Status column is available. I am looooooooooooooooooost :) – user1765689 Oct 22 '12 at 17:43
  • I really don't understand what the problem could be... have you tried to enter the function searchReplaced? And if you enter ?searchReplaced("somevalue") on the VBA console, what do you get? – fthiella Oct 22 '12 at 18:10