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: 845
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,361
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: 845
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
 
LilAnnCC1
post Aug 20 2019, 10:01 AM
Post#4



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


Okay, all. I have found that my procedure is not working as I need it to (please see other posts in this thread).

I am wondering if I have to restructure my BOM tables?

The main BOM table called tBOM has the following:

ID PK
PartID FK---usually a finshed good part or assembly part
BOM nvarchar(25)
...a few other fields

I am not using this table in my recursive bom cte at all. The BOMID in the details table goes back to the tBOM table.

The BOM Detail table called tBOMDetail has the following:

ID PK
BOMID FK (to tBOM)
SubBOMID FK (to tBOM)--can be null also known as sub BOM
PartID FK--usually a raw material part or an assembly part
bomQTY

I need help in determining if this is the correct way to be able to do a recursive on my materials list. I'm thinking that I might not have this structured properly.

The SubBOMID means that the part is actually a BOM in the tBOM table, but it could belong to several BOM's, not just the BOM I want a material list for.

My recursive cte works for most Fixture BOMs up to 3 levels, but it fails on assembly BOM's (maximum recursion).

Should I create another table as a join? How should I restructure these tables to be able to get a correct material list (possibly going to 4 levels)?










--------------------
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    21st November 2019 - 03:53 AM