I'm having trouble creating my query
I have a table representing the company's products. Ever once in a while the products get replaced by a newer product. Like below
_____________________________
Product - Replaces - Replaced By
a..................................b
--------|----------|-----------
b.................a...............e
--------|----------|------------
c
--------|----------|------------
d
------------------------------
e.................b

Can someone help me write a query so the result below will be shown in
three fields.

Product - Replaced by level 1 - Replaced by Level 2-Replaced by level 3
....a..................b.......................... . e......................and so on

Yes and no.

If there is a maximum number of levels, you just keep adding a copy of your table to a query for each level and join the copies on the replaces field. However the data returned will be limited by the number of copies you have added. Eventually, depending on how far you go, you will loose replacements from this.

If you want to support an infinite number of levels, you will need to build the dataset using code. This is beyond my capabilities but has been covered in these forums before (notably by Pat Hartman).

Note that if your application is built properly, you don't need three fields to track replacements, only two. You either track the parent or the child, not both.

Thanx for the response...
Hopefully there will only be about 10 levels

Can you help me write the query..
The name of the table is "Main Document" and the query is named "Replaced By"

Field 1 contains all products

When I write this in field 2 it works for the first level: Replaced By Level 1: DLookup("[Product]";"[Main Document]";"[Replaces]=" & "'" & [Product] & "'")

What should i write in field3??

Have a look at the attached. This is three levels but you will see what I mean.

Thanks

works perfect