If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Help with complex query
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 |
#2
|
|||
|
|||
Help with complex query
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; -- Build a little, test a little. "Cam" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|