Query for finding relation between 2 fields

[LudoS]

Hi All, I hope someone is able to help me with this.

I am having a table with partnumber(s) like shown here

ID (autonumber, primary key)
Partnumber (text)
Replacment (text)
Description (text)
Comment (text)
..... <Some other fields>.

Data look like this:
ID Partnumber Replacement Description Comment
x 5030331-001 Partx
x 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
x 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
x 5030333-001 Partx
x .....
x 8063737-001 Part1 from vendor2

There are a lot of Partnumbers which are obsolete (this partnumber has a replacement part). I am searching for a query where (with one parameter, = partnumber to look for), where i can see complete relation. So if I am searching for partnumber 8063737-001 I get same result as when I am searching for 5030332-001 or 5030332-002.

Result should be like this:
ID Partnumber Replacment Description Comment ...........
x 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
x 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
x 8063737-001 Part1 from vendor2


has anyone any idea how to create this kind of query?

Ludo

[Delerna]

Maybe you should post your query also, because I can't clearly decipher what you are asking above.


Also, maybe you can explain this

I get same result as when I am searching for 5030332-001 or 5030332-002.

in a bit more detail because if

So if I am searching for partnumber 8063737-001

then I don't get why the result would look like

ID Partnumber Replacment Description Comment ...........
x 5030332-001 5030332-002 Part1 from vendor 1 Obsolete
x 5030332-002 8063737-001 Part1 from vendor 1 Obsolete
x 8063737-001 Part1 from vendor2

I would have thougt the result should look like

ID Partnumber Replacment Description Comment ...........
x 5030332-001 5030332-002 Part1 from vendor 1 Obsolete

when the parameter= 5030332-001



OR


ID Partnumber Replacment Description Comment ...........
x 5030332-002 8063737-001 Part1 from vendor 1 Obsolete

when the parameter= 5030332-002


or

ID Partnumber Replacment Description Comment ...........
x 8063737-001 Part1 from vendor2

when the parameter= 8063737-001

[LudoS]

Hi,

The from word was just as a text "part1 from vendor 1".
This was only an example, I have thousands of partnumber(s). Partnumbers are correlated to product-table. Most of the time a partnumber without a replacement part (replacement = empty). Sometimes we want to see the history of these partnumber(s). So for example when user is requesting this, I wnat to have the result like this:

partnumber, replacement, (other fields)
x1, x2, ....
x2, x3, ....
x3, x4, ....
x4, <empty>, ...

The number of rows (result) can 1 to ???

repeat
........
Select * from partTable where partnumber=@paramater or replacement=@paramater
......
until <everything has been found>

Something like this, it is not right but don't know how to express it more clearly.

<everything has been found>
1. part has no replacement AND [last row, like x4]
2. partnumber from 1st could not be found in replacement [first row, like x1]


In vba I think it could be quite easy routine, but i like to create some kind of query.

[NeoPa]

Basically, you need to add your table (We'll refer to it as tblPartNo as you haven't told us what it is) once for each part in the first place, then again for each level of redirection you want to cover.

Each table would have a (LEFT JOIN) link from its [Replacement] field to the [PartNumber] field of the next copy of the table.

If you start from there it will soon become clear what you are dealing with and how to proceed from there.