Jiwa

Hi, I have an issue where a client is processing GRN's to Jobs using the SAME PartNo for multiple lines, each line being for a different Job. I am trying to get the link to work between RE_Lines and JB_CostEntryLines using the PackSlipID and InventoryID to join which works fine if different Partno's are used for each line! However in this scenario there are 9 RE Lines (same PartNo) and my query below returns 81 lines!! 9 x9.
has anyone ideas to correct this, I can't see any other distinguishing field that can ne used to qualify?

Cheers

Code: Select all

SELECT RE_Lines.LineNumber, JB_CostEntryLines.Qty, JB_CostEntryLines.UnitCost, RE_Lines.SuppliersCost, JB_CostEntryLines.ComponentNo, JB_CostEntryLines.ComponentDesc, RE_Lines.Description, RE_Main.SlipNo,
JB_Main.JobNo
FROM RE_Main INNER JOIN
RE_Lines ON RE_Main.PackSlipID = RE_Lines.PackSlipID FULL OUTER JOIN
JB_CostEntryLines INNER JOIN
JB_Main ON JB_CostEntryLines.JobID = JB_Main.JobID ON RE_Lines.InventoryID = JB_CostEntryLines.ComponentID AND RE_Lines.PackSlipID = JB_CostEntryLines.LinkID
WHERE (RE_Main.SlipNo = '275250/INV-0772')
ORDER BY RE_Lines.LineNumber


I use this query for the link between a GRN line (RE_Lines) and a Job Costing job (JB_Main):

Code: Select all

SELECT RE_Main.SlipNo, RE_Lines.LineNumber, RE_Lines.PartNo, RE_Lines.Description, RE_Lines.JobCharge, JB_Main.JobNo, JB_CostCentres.CostCentreNo, JB_Stages.StageNo
FROM RE_Main
JOIN RE_Lines ON RE_Main.PackSlipID = RE_Lines.PackSlipID
JOIN JB_JobStages ON RE_Lines.JB_JobStages_RecID = JB_JobStages.RecID
JOIN JB_Stages ON JB_JobStages.JB_Stages_RecID = JB_Stages.RecID
JOIN JB_JobCostCentres ON JB_JobStages.JB_JobCostCentres_RecID = JB_JobCostCentres.RecID
JOIN JB_CostCentres ON JB_JobCostCentres.JB_CostCentres_RecID = JB_CostCentres.RecID
JOIN JB_Main ON JB_JobCostCentres.JobID = JB_Main.JobID


Does this help?

Hi Scott,
Thanks for the reply. Unfortunately it doesn't really help as I need the link between RE_Lines and JB_CostEntryLines (even if Null). The issue is if RE_Lines uses the same partno for multiple lines then my links duplicate the same PartNo lines that many times? I can't seem to link a one to one relationship?

I'm with you now. The Job Costing data structures are far from ideal. But here's what you need:

Code: Select all

SELECT RE_Lines.LineNumber, JB_CostEntryLines.Qty, JB_CostEntryLines.UnitCost, RE_Lines.SuppliersCost, JB_CostEntryLines.ComponentNo, JB_CostEntryLines.ComponentDesc, RE_Lines.Description, RE_Main.SlipNo, JB_Main.JobNo
FROM RE_Main
JOIN JB_CostEntryLines ON RE_Main.PackSlipID = JB_CostEntryLines.LinkID
JOIN JB_Main ON JB_CostEntryLines.JobID = JB_Main.JobID
JOIN RE_Lines ON (RE_Main.PackSlipID = RE_Lines.PackSlipID AND JB_CostEntryLines.ComponentID = RE_Lines.InventoryID AND RE_Lines.JB_JobStages_RecID IN (SELECT StageID FROM JB_JobStages WHERE CostCentreID IN (SELECT CostCentreID FROM JB_JobCostCentres WHERE JobID = JB_Main.JobID)))
WHERE RE_Main.SlipNo = '275250/INV-0772'
ORDER BY RE_Lines.LineNumber


The above assumes that there is NOT multiple receival lines that have the SAME part no AND are both for the SAME job.

Awesome, thanks Scott!