BAQ Duplication

Kinetic ERP
,
1

We have customer that require we provide them with the cost broken out into Labor and Material for each “section”. I have the BAQ put together but for some reason it is duplicating the records multiple times which is displaying the incorrect amount when it is summed together as I have been grouping them by the Assembly.

select 
	[QuoteOpr].[Company] as [QuoteOpr_Company],
	[QuoteHed].[QuoteNum] as [QuoteHed_QuoteNum],
	[Customer].[Name] as [Customer_Name],
	[QuoteOpr].[OprSeq] as [QuoteOpr_OprSeq],
	[QuoteMtl].[PartNum] as [QuoteMtl_PartNum],
	[QuoteMtl].[QtyPer] as [QuoteMtl_QtyPer],
	[QuoteMtl].[EstUnitCost] as [QuoteMtl_EstUnitCost]
from Erp.QuoteHed as QuoteHed
inner join Erp.QuoteMtl as QuoteMtl on 
	QuoteHed.Company = QuoteMtl.Company
	and QuoteHed.QuoteNum = QuoteMtl.QuoteNum
inner join Erp.QuoteOpr as QuoteOpr on 
	QuoteHed.Company = QuoteOpr.Company
	and QuoteHed.QuoteNum = QuoteOpr.QuoteNum
inner join Erp.Customer as Customer on 
	QuoteHed.Company = Customer.Company
	and QuoteHed.CustNum = Customer.CustNum
where (QuoteHed.QuoteNum = @Quote_Number)

MicrosoftTeams-image (1)

In the image, the part numbers are being duplicated for each of the operations despite them being used in the operation. Some of the operations have no parts and it’s only labor which should result in 0 but upon grouping, all of the material cost goes to 200.00.

What am I doing wrong?

2

This looks like it will create a row for every operation material combination.

You need to either SUM the operation and material costs in separate subqueries or even better just use the JobAsmbl (sp?) table.

JobAsmbl has fields with total values for labor, burden and material costs for each assembly on the job.

*Side question. Are you marking up the costs before presenting this data to the customer?

3

I’ll definitely check out the JobAsmbl table! I had also tried to use the Quote Analysis Export to generate the data and create a BAQ to pull the data for a report but I was having difficulty there too with it duplicating for some reason.

We have a multi-company setup and one company is utilizing assemblies and summing the data in that category while another company is summing this data up via operations and do not utilize the assembly structure outside of the Asm 0. They add all of their parts under a single operation while the other company may have many of the same operation under different assemblies. So grouping by operation with that company wouldn’t work.

Regarding the markup/profit, I have a few different attempts at this query in a few different methods and seem to hit the same duplicating snag. In the first test query that I created, I did bring in the QuoteQty table to perform the math on the cost. This examle didn’t use that but would that be the correct table to grab this information from?

edit: The reason I can’t use the JobAsmbl table is because this needs to be performed prior to this being a job. They want to see this detail on/accompanying the quote and then they would approve it and be picked up by MRP.