query question

[Joel Allen]

Hello,

I have having trouble with this query, and am not sure how to go about it.

In my table that's linked to another shipping database, I have a main order
which is 221001. At one point, Shirt, Pants, and Shoes were all under this
order. Pants and Shoes were moved to another order from the main order thus
creating order 21565. Then later, the Shoes were moved to another order
which was 21566. Below is diagram:

221001
|
|
|
|---21565
|
|
|
|------21566

The table looks like this:

Order Item MovedFromOrder

221001 Shirt
21565 Pants 221001
21566 Shoes 21565

My goal is to create a query that looks like this, but it's tough because
I'm not sure how to associate 21566 back to the main order:


Order Item MovedToOrder

221001 Shirt
221001 Pants 21565
221001 Shoes 21566

Thanks very much,
Joel

 

[Michel Walsh]

Jet does not allow recursion (while MS SQL Server 2005 does). If there is at
most three levels, you can use joins:



SELECT a.Item,
Nz(c.Order, Nz(b.Order, a.Order)) AS OriginalOrder,
a.Order As MovedTo

FROM (myTable AS a LEFT JOIN Table11 AS b ON a= b.Order)
LEFT JOIN Table11 AS c ON b.MovedFrom = c.Order;



Hoping it may help,
Vanderghast, Access MVP