UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Help With Cte Recursive Bom, Any Versions    
 
   
LilAnnCC1
post Aug 15 2019, 10:20 AM
Post#1



Posts: 819
Joined: 31-May 04
From: Wisconsin, USA


Hello all! I'm using Access 2017 as a front end and SQL Server 2017.

I have a table called tBOMDetail. These are the columns in it:

ID----------------PK
BOMID----------FK, Not Null--------to tBOM
SubBOMID-----FK, Null-------------to tBOM
PartID----------FK, Not Null--------to tPart
bomQTY--------Not Null

I am trying to build a Bill of Materials, but I've managed to confuse myself and need you guys to smack me!

I have a BOM with an ID of 1583 that has 21 Parts in it--but this includes 8 SubBOMID (sub-assemblies). Each of these 8 SubBOMID are actually a BOMID in the tBOM table and have their own list of parts and quantities as they can be used in other productions.

So, first I get a query that gives me all the parts for this BOM where the SubBOMID IS NULL

Then I need just the QTY of the Parts where the SubBOMID IS NOT NULL (I do not want these PartIDs)

Then I need a list of Parts from the SubBOMID (which would actually be it's own BOMID) and that QTY * the QTY above and combine it with the first query.

I have tried using just the BOM Detail table and then I tried using the Part table. I am just going around in circles.

I appreciate your time and any suggestions!










--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Aug 15 2019, 12:53 PM
Post#2



Posts: 3,206
Joined: 27-February 09



If you google for "'recursive CTE' and 'bill of materials'", there are examples all over.
https://www.experts-exchange.com/questions/...-Materials.html
Go to the top of the page
 
LilAnnCC1
post Aug 15 2019, 01:06 PM
Post#3



Posts: 819
Joined: 31-May 04
From: Wisconsin, USA


Thank you all. I think I finally got it!

Here is my final code that seems to work so far (will do more testing)

CODE
ALTER Procedure [dbo].[spBOMMaterialList]
@BOM int

AS
SET NOCOUNT ON;

WITH CTE_BOM AS (
SELECT ID, BOMID, SubBOMID, PartID, bomQTY, bomLevel
FROM tBOMDetail
WHERE BOMID=@BOM

UNION ALL

SELECT d.ID, d.BOMID, d.SubBOMID, d.PartID, b.bomQTY * d.bomQTY as Needed, b.bomlevel
FROM tBOMDetail d
INNER JOIN CTE_BOM b on d.BOMID=b.subBOMID
WHERE b.BOMID=@BOM)

SELECT PartID, p.ItemNo, Sum(b.bomQTY) as Needed
FROM CTE_BOM b
INNER JOIN tPart p on b.PartID=p.ID
WHERE SubBOMID is null
GROUP BY PartID, ItemNo
ORDER BY ItemNo;
GO


I appreciate all your help!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    20th August 2019 - 02:17 AM