Help with complex query 02-19-10

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
0
Utf
2/19/2010 6:56:01 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
794 Views

Similar Articles

[PageSpeed] 38

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
0
Utf
2/20/2010 5:33:01 AM
Reply:

Similar Artilces: