Complex Query

[aarellano]

Hello,

I really don't know how to tacle this one.

I have 3 tables
Routing, ProductStructure and WorkCenter

Routing table has the Item# plus the work Center(where labor hours are kept)
Product Structure has the Parent item# plus Component item number.

I am linking Routing to Product Structure by Item# and Parent Item#, then the Routing to the WorkCenter by workcenter# to workcenter#

up to this point things seem to work well in simple parts.

for example
Part# Component WorkCenter Labor
123 ab 123ab .87

But for example item 123AB

consists of item 123 plus other components
and I cannot get all the components

I know it might sound very confusing

 

[lespaul]

the best way to get help is to provide sample data from your tables and your expected results from that data....

[tt]
Routing
Item# WorkCenter
data data
data data
data data

ProductStructure
Item# ParentItem#
data data
data data
data data

WorkCenter
WorkCenter (other fields?)
data
data
data

RESULTS:
TableName.FieldName TableName.FieldName
data data
data data
data data
data data
[/tt]

and try to provide examples that show the "problem" you are having....I'm guessing your problem has something to do with being able to get all the component pieces to list?



Leslie

In an open world there's no need for windows and gates

http://www.ubuntu.com

 

[GirlBlonde]

give us an example of what you are trying to achieve as Leslie said I'm sure we can figure it out

Energy & persistance conquer all things!

 

[aarellano]

I am so sorry that I had not gotten back but I have been very very busy, Inventory time plus being sick. But enough excuses.

ok let me see if I can explain it
I am trying to capture my labor hours for each item produced but I am runing into poroblems where the item has component items that have hours on them
for example we have an item B5247CF
if I look at the product structure file it gives me this data(up to this point there are not labor hours)

 Parent           Component  
 Item             Item number
 Number                      
 B5247CF          LBAG       
 B5247CF          5247CF

this tells me that this item is bagged and it has a component item then I look at the routing for this item(this will give me the hours)

Item number      Operation   Run     Time  
                 Sequence    Labor   Basis 
                 Number              Code  
B5247CF            0020       1.67     3

this tells me that item B5247CF takes 1000/1.67 hrs to be bagged. Then since I have a component item I have to look at the routing for that item 5247CF (note no B)
the product structure for this is

Parent           Component   
Item             Item number 
Number                       
5247CF           PF-835-B11  
5247CF           I0212

and the routing 5247CF

5247CF             0001        .84     3
5247CF             0011     189.00     P
5247CF             0015       2.00     4

Up to this point if I do a query between the product structure and the routing I get this

Parent           Component         Run     Time 
Item             Item number       Labor   Basis
Number                                     Code 
B5247CF          LBAG                .00        
B5247CF          5247CF              .84     3  
B5247CF          5247CF           189.00     P  
B5247CF          5247CF             2.00     4

The problem is that if you take a look at the product structure for item 5247CF we have components within the item

5247CF PF-835-B11
5247CF I0212

the routing for both of these components is

Item number      Operation   Run     Time 
                 Sequence    Labor   Basis
                 Number              Code 
PF-835-B11         0001     599.98     P

so
when I build my query I would get

Parent           Component         Run     Time 
Item             Item number       Labor   Basis
Number                                     Code 
B5247CF          LBAG                .00        
B5247CF          5247CF              .84     3  
B5247CF          5247CF           189.00     P  
B5247CF          5247CF             2.00     4 
 5247CF          PF-835-B11       599.98     P

I have to look at the routing for both of these components
and I have no idea how to go about doing this