Help with complex query

[Cam]

Hello,

I have a Routing query which show all seq of step to complete the part.
Another query that show production WIP status. I want to be able to tie them
together so that I can show current orders in each seq of the Routing. I
tried, tried, but couldn't come up with a way to do this.
Any help is appreciated. Here is the sample data.

Routing query/table:
Part Seq Area Mach
ABC 10 Drill 2020
ABC 20 Turn 1060
ABC 30 Inspect
ABC 40 Ship
ABC 50 Complete

ProductionWIP query/table:
Part Seq Area Mach Order Qty
ABC 10 Drill 2020 111200 1
ABC 10 Drill 2020 111201 2
ABC 10 Drill 2020 111205 1
ABC 30 Inspect 111212 2
ABC 40 Ship 111008 2
ABC 40 Ship 111050 1

Resulting query: (would look like in Excel or pivot view)
Part Seq Area Mach Order
ABC 10 Drill 2020 111200 111201 111205
ABC 20 Turn 1060
ABC 30 Inspect 111212
ABC 40 Ship 111008 111050
ABC 50 Complete

Thanks

 

[KARL DEWEY]

Try these queries --
ProductionWIP_X
SELECT Routing.Part, Routing.Seq, Routing.Area, Routing.Mach,
ProductionWIP.Order, ProductionWIP.Qty
FROM Routing LEFT JOIN ProductionWIP ON (Routing.Area = ProductionWIP.Area)
AND (Routing.Seq = ProductionWIP.Seq) AND (Routing.Part = ProductionWIP.Part);

ProductionWIP_Y
SELECT ProductionWIP_X.Part, ProductionWIP_X.Seq, ProductionWIP_X.Area,
ProductionWIP_X.Mach, ProductionWIP_X.Order, (SELECT Count(*) FROM
ProductionWIP_X AS [XX] WHERE ProductionWIP_X.Part = [XX].Part AND
ProductionWIP_X.Seq = [XX].Seq AND ProductionWIP_X.Order > [XX].Order)+1 AS
Rank
FROM ProductionWIP_X
ORDER BY ProductionWIP_X.Part, ProductionWIP_X.Seq, ProductionWIP_X.Area,
ProductionWIP_X.Mach, ProductionWIP_X.Order;

SELECT ProductionWIP_Y.Part, ProductionWIP_Y.Seq, ProductionWIP_Y.Area,
ProductionWIP_Y.Mach, IIF(ProductionWIP_Y.Rank =1, ProductionWIP_Y.Order,
"") AS ORDER_1, IIF(ProductionWIP_Y.Rank =2, ProductionWIP_Y.Order, "") AS
ORDER_2, IIF(ProductionWIP_Y.Rank =3, ProductionWIP_Y.Order, "") AS
ORDER_3, IIF(ProductionWIP_Y.Rank =4, ProductionWIP_Y.Order, "") AS ORDER_4
FROM ProductionWIP_Y;