The material unit cost is different, that’s why you get “duplicate” rows, they aren’t the same. If you want get that combined into single rows, you need to use aggregate functions ( like Sum() or Avg() etc…) and then group by the other fields.
1 Like
The join type is sometimes the cause of duplicates, as well as having the join based on too many ‘joined on’ fields involved.
So you can change join type to Left Join on the more important table (Selecting all the relevant records from it, and only the records in common from the other table(s), along with following Brandon’s advice of adding columns as a trouble shooting step. That can reveal why there appear to be 4 ‘duplicates’ that are actually 4 records that all share most of the same fields you included but with one field with 4 differing values. and again, review the fields that the ‘most important’ table is joined on.
The Epicor BAQ editor is essentially helping you to write a SQL select statement, with a visual bag of tools. It isn’t writing SQL precisely in syntax but it is very close as you will see on the , and it does have exceptions that Epicor doesn’t allow but don’t worry about that just yet. There is an enormous amount of free training on using relational databases and specifically MS-SQL (under the skin of your implementation) so finding and reading some of that will also help develop and sharpen your query writing intuition.
2 Likes
Back up a second and look at your data. Make some test BAQ’s and limit each table by the a job and part number that you are looking at. Take those lines that you get from each of the table and do the “joins” manually. How would a computer know which line goes with each? What do you want to happen if one of those tables has more than one line? You need to understand the data before you can make a query.
3 Likes
Thats what I thought too initially.
Yes, this report is being made to compare the Material Unit Cost from the Chart Tracker with the Calculated Scrapped cost. And this actually helped, besides the BAQ I was also trying this out in SQL and using an aggregate function with the grouped by Job number might actually help. Thank you.
Also, you might have duplicate material items (same PN) as different qty counts or multiple times as separate materials in the same job operation.
Definitely!! I have some time allotted today to take a look at different joins, I have worked with SQL but never with a database I wasn’t familiar with and not for writing queries in a professional setting.
Thank you for your advice:)
I completely agree, I do not know the data at all, I have been working with Epicor for 3 weeks coming on to 4 weeks this week. I did the working with single tables thing but I will try it again with a more calm mind. Thank you 
1 Like
After looking at the data based on your query you need to add two conditions to get better result set.
EDIT Missed one thing
Part ActTranQty <0
To Join PartTran.PartNum=JobMtl.PartNum
Try adding those conditions and check your results.
Also you can grab the PartTran.ExtCost and PartTran.ActTranQty instead of calculating
And you might add PartTran.ActTransUOM
2 Likes
select top 100
parttran.TranNum,
[LaborDtl].[Complete] as [LaborDtl_Complete],
[LaborDtl].[ClockInDate] as [LaborDtl_ClockInDate],
[LaborDtl].[JobNum] as [LaborDtl_JobNum],
[PartTran].[PartNum] as [PartTran_PartNum],
LaborDtl.ScrapQty as ScrapQty,
-PartTran.ActTranQty as CalcMtlScrap,
-PartTran.ExtCost as ExtScrapCost,
(LaborDtl.ScrapQty * JobMtl.QtyPer) as [Calculated_Material_Scrap],
((LaborDtl.ScrapQty * JobMtl.QtyPer) * JobMtl.EstUnitCost) as [Calculated_Scrap_Cost],
[PartTran].[MtlUnitCost] as [PartTran_MtlUnitCost]
from Erp.LaborDtl as LaborDtl
inner join Erp.PartTran as PartTran on
LaborDtl.Company = PartTran.Company
and LaborDtl.JobNum = PartTran.JobNum
inner join Erp.JobMtl as JobMtl on
PartTran.Company = JobMtl.Company
and PartTran.JobNum = JobMtl.JobNum
and PartTran.PartNum=jJobMtl.PartNum
WHERE LaborDtl.ScrapQty >0 and parttran.acttranqty<0
This appears to be a simple one for what you wanted I was wrong about the QtyBearing that was due to the missing join in my dataset.
I left your calculations in…
OMG! This is so nice of you. Thank you for this. I will try it and let you know if it works on my end.
2 Likes