X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
2 Pages V < 1 2  (Go to first unread post)
   Reply to this topicStart new topic
> For Each/loop?, Any Versions    
post Oct 14 2019, 11:10 PM

Posts: 3,367
Joined: 27-February 09

Just to be clear about what I'm asking for...
Say take a Product and it breaks down into 3 sub-assemblies (and maybe some random parts), and the sub-assemblies break down to a collection of parts. I think two Products should be enough.

I'm only asking because it's actually really hard to make up stuff like this. (or maybe I just lack imagination!) Once you get the lowest level parts (parts that do not consist of other parts), then you're done. So you do execute the BOM code/common table expression, and it returns all parts at all levels (so some are subassemblies, and some are "final"/lowest level parts). So then what you have to do is remove all the parts that are subassemblies - and those are listed on the Bill of materials. The AssemblyID is just a part number that has "child parts". So you remove all of those.

So the final/lowest level parts is something like
SELECT PartID FROM Parts WHERE PartID NOT IN (SELECT SubAssemblyID FROM bom WHERE ProductID = @ProductID)… but on your case, there might be a table of PartIDs… not totally sure yet. (it's a brain twister, but if you post data for Jeff Moden per his instructions, you'll get an answer - super nice guy and crazy smart.)

Any chance I can get a couple of work orders and the Products (and the components) for them. It's really wonky, but the way you determine if a Product is a bottom-level component is that it's not an assembly (So the key for that Product doesn't appear in the list of Assemblies. If you have an account on SQLServerCentral, read this thread Some folk on there are twits. Joe Celko is super smart, but a real piece of work. Well known for being incredibly obnoxious.
Go to the top of the page
2 Pages V < 1 2

Custom Search

RSSSearch   Top   Lo-Fi    15th December 2019 - 10:21 AM