BAQ results on two lines rather than on 1 line

Epicor ERP 10
21

Alright, I’ve moved on once again after reading your “wizard” document. I get it now about the TranType’s and have fixed that issue. But I am now receiving another error:
Severity: Error, Table: , Field: , RowID: , Text: The multi-part identifier “PartTran.SysDate” could not be bound.
The multi-part identifier “PartTran.SysDate” could not be bound.

I’m including the code I have thus far:
select
[PartQty].[PartNum] as [PartQty_PartNum],
[PartQty].[OnHandQty] as [PartQty_OnHandQty],
[SubQuery1].[Calculated_LastProdDate] as [Calculated_LastProdDate],
[SubQuery3].[Calculated_LastDateCust] as [Calculated_LastDateCust],
[SubQuery4].[Calculated_LastStk2Mtl] as [Calculated_LastStk2Mtl]
from Erp.PartQty as PartQty
inner join (select
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranType] as [PartTran_TranType],
(max( PartTran.SysDate )) as [Calculated_LastProdDate]
from Erp.PartTran as PartTran
where (PartTran.TranType = ‘STK-CUS’)
group by [PartTran].[PartNum],
[PartTran].[TranType]) as SubQuery1 on
SubQuery1.PartTran_PartNum = PartQty.PartNum

inner join  (select 
	[PartTran1].[PartNum] as [PartTran1_PartNum],
	[PartTran1].[TranType] as [PartTran1_TranType],
	(max( PartTran.SysDate )) as [Calculated_LastDateCust]
from Erp.PartTran as PartTran1
 where (PartTran1.TranType = 'MFG-STK')
group by [PartTran1].[PartNum],
	[PartTran1].[TranType])  as SubQuery3 on 
	SubQuery3.PartTran1_PartNum = PartQty.PartNum

inner join  (select 
	[PartTran2].[PartNum] as [PartTran2_PartNum],
	[PartTran2].[TranType] as [PartTran2_TranType],
	(max( PartTran.SysDate )) as [Calculated_LastStk2Mtl]
from Erp.PartTran as PartTran2
 where (PartTran2.TranType = 'STK-MTL')
group by [PartTran2].[PartNum],
	[PartTran2].[TranType])  as SubQuery4 on 
	SubQuery4.PartTran2_PartNum = PartQty.PartNum

inner join Erp.PartCost as PartCost on 
	PartCost.Company = PartQty.Company
And
	PartCost.PartNum = PartQty.PartNum
22

The max lines in the section below should match the tables that are selected for the sub query. See where [PartTran2].[PartNum] as [PartTran2_PartNum] say PartTran2 (that’s that alias thing you had trouble with earlier.) The Max() needs to have PartTran2 as well so it’s looking at the same table. Change that to match and see if it works. You’ll have to watch out for that on copy and paste because if you are moving from one sub to a different one, different tables on the sheets need to have unique aliases. See the screen shot below to see how I make sure to only use available fields.

inner join  (select 
	[PartTran2].[PartNum] as [PartTran2_PartNum],
	[PartTran2].[TranType] as [PartTran2_TranType],
	(max( PartTran.SysDate )) as [Calculated_LastStk2Mtl]

You’ll have to watch out for that on copy and paste because if you are moving from one sub to a different one, different tables on the sheets need to have unique aliases. See the screen shot below to see how I make sure to only use available fields.

image.png1093×651 35 KB