The main assembly on the job has 6 different operations needed to complete it, one of which is machine shop. under that assembly there are 8 subassemblies, some of which may be assigned to that top level machine shop operation. Some of those have subassemblies under them that may require a machine shop operation, which I have nested under them.
Here’s what my structure generally looks like, notice how the first subassembly is not assigned to a specific op(although it will probably end up being MECHASSM), but one of it’s subassemblies has a MACHSHOP op. I would need that part, ASM: 16, to show up in my parts list, with Mtl 10 listed as a material, and a Used On column that shows ASM: 5’s name.
sorry for the long explanation I hope I cleared up what I’m trying to do. here’s the query phrase, I’ll check out that second BAQ and let you know how it works.
select
(0) as [Calculated_DrawingPrinted],
[JobAsmbl].[JobNum] as [JobAsmbl_JobNum],
[JobAsmbl].[RequiredQty] as [JobAsmbl_RequiredQty],
[JobAsmbl].[PartNum] as [JobAsmbl_PartNum],
[JobAsmbl].[Description] as [JobAsmbl_Description],
[JobOper].[DueDate] as [JobOper_DueDate],
[JobMtl].[PartNum] as [JobMtl_PartNum],
[JobMtl].[Description] as [JobMtl_Description],
[JobAsmbl].[TLASetupHours] as [JobAsmbl_TLASetupHours],
[JobAsmbl].[TLAProdHours] as [JobAsmbl_TLAProdHours],
(0) as [Calculated_Programmed],
[JobAsmbl].[AssemblySeq] as [JobAsmbl_AssemblySeq],
[JobAsmbl].[Parent] as [JobAsmbl_Parent],
(5) as [Calculated_Priority],
[JobOper].[OpCode] as [JobOper_OpCode]
from Erp.JobAsmbl as JobAsmbl
inner join Erp.JobMtl as JobMtl on
JobAsmbl.Company = JobMtl.Company
And
JobAsmbl.JobNum = JobMtl.JobNum
And
JobAsmbl.AssemblySeq = JobMtl.AssemblySeq
inner join Erp.JobOper as JobOper on
JobAsmbl.Company = JobOper.Company
And
JobAsmbl.JobNum = JobOper.JobNum
And
JobAsmbl.AssemblySeq = JobOper.AssemblySeq
and ( JobOper.DueDate is not null and JobOper.OpCode = ‘MACHSHOP’ )
where (not JobAsmbl.AssemblySeq = 0)
and JobAsmbl.Company = ANY (select JobAsmbl1_Company from ((select
[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
[JobAsmbl1].[Parent] as [JobAsmbl1_Parent],
[JobAsmbl1].[Company] as [JobAsmbl1_Company],
[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum]
from Erp.JobAsmbl as JobAsmbl1)) as SubQuery2) and JobAsmbl.JobNum = ANY (select JobAsmbl1_JobNum from ((select
[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
[JobAsmbl1].[Parent] as [JobAsmbl1_Parent],
[JobAsmbl1].[Company] as [JobAsmbl1_Company],
[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum]
from Erp.JobAsmbl as JobAsmbl1)) as SubQuery2) and JobAsmbl.AssemblySeq = ANY (select JobAsmbl1_Parent from ((select
[JobAsmbl1].[PartNum] as [JobAsmbl1_PartNum],
[JobAsmbl1].[AssemblySeq] as [JobAsmbl1_AssemblySeq],
[JobAsmbl1].[Parent] as [JobAsmbl1_Parent],
[JobAsmbl1].[Company] as [JobAsmbl1_Company],
[JobAsmbl1].[JobNum] as [JobAsmbl1_JobNum]
from Erp.JobAsmbl as JobAsmbl1)) as SubQuery2)
order by JobOper.DueDate