Ok, i'm a little confused. Your query,...
andy.dawson (10/24/2007)
SELECT [Field5]*[Enter Build Quantity] AS Expr1, BOM.Field1, BOM.Field2, BOM.Field3, BOM.Field4, BOM.Field5, BOM.Field6, Stock.stTempBinLocation, Stock.stUser1FROM BOM INNER JOIN Stock ON BOM.Field3=Stock.stStockCode
WHERE (((BOM.Field1)=[Enter Part Number]) AND ((BOM.Field2)<>"D") AND ((BOM.Field6) Like "S/A*"))
wants to look at lines of the BOM where field6 is LIKE "S/A" and field 2 <> "D" which would mean you want to see all records for each parent stock code where field 2 = "P" or "M" *AND* field 6 LIKE "S/A". So, you have indicated you want to see the below lines for the parent stock code 'TX5922.AA'
P = Part
M = Manufactured Item
D = Description Only Item
....
So, as per my previous post, I would enter the Stock Code TX5922.AA and I would get the following results:
P5430.06 S/A of PCB (which is in this level of the BOM)
P5431.06 S/A of PCB (which is in this level of the BOM)
However, there is another S/A of PCB within this build but it is in a different S/A at a lower level. The result should be:
P5430.06 S/A of PCB (which is in this level of the BOM)
P5431.06 S/A of PCB (which is in this level of the BOM)
P5431.94 S/A of PCB (which is in a sub-level of the BOM - being P5431.09.01)
My question is why would you also not want to see line 2 of your attachment, where parent code is TX5922.AA, field 2 = 'M' and field 6 is LIKE 'S/A%'? And line 5, the orange line also fits the criteria. I understand that you are taking that line into account by wanting to call line 41, but by your criteria line 5 should be returned as well. Do you only want to see records where field6 LIKE 'S/A OF PCB%'? Forgive me for maybe seeming dense, but I am trying to understand what you want.
Greg
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.