ACCESS FEED: Re: [MS_AccessPros] Multilevel BOM explosion

Jumat, 29 Maret 2013

Re: [MS_AccessPros] Multilevel BOM explosion

Hi John,

I'll give it a try. I'll keep you posted.

The RM's are the end of the chain. The qty on the intermediate parts is always 1.

--- In MS_Access_Professionals@yahoogroups.com, John Viescas <JohnV@...> wrote:
>
> Luis-
>
>
>
> Are there any records with RM1, RM2, etc. in field_Product? In other words,
> is RM1 the end of the chain? Is there any case where an intermediate part
> has a quantity other than 1?
>
>
>
> Assuming that your answers to the above are Yes, then you could try:
>
>
>
> SELECT One.field_Product, IIf(Not IsNull(Four.field_Product),
> Four.field_Product,
>
> IIf(Not IsNull(Three.field_Product), Three.field_Product,
>
> IIf(Not IsNull(Two.field_Product), Two.field_Product,
> One.field_Component))) As Component,
>
> NZ(Four.field_Qty, 1) * NZ(Three.field_Qty, 1) * NZ(Two.field_Qty, 1) *
> One.field_Qty As Qty
>
> FROM ((tblBOM As One LEFT JOIN
>
> tblBOM As Two ON Two.field_component = One.field_Product)
>
> LEFT JOIN tblBOM As Three On Three.field_Component = Two.field_Product)
>
> LEFT JOIN tblBOM As Four On Four.field_Component = Three.field_Product
>
> WHERE NOT EXISTS (SELECT field_Product FROM tblBOM As Test
>
> WHERE Test.field_Component = One.field_Product)
>
>
>
> The above assumes that a BOM chain goes no more than 4 levels deep.
>
>
>
> John Viescas, Author
>
> Microsoft Access 2010 Inside Out
>
> Microsoft Access 2007 Inside Out
>
> Microsoft Access 2003 Inside Out
>
> Building Microsoft Access Applications
>
> SQL Queries for Mere Mortals
>
> http://www.viescas.com/
>
> (Paris, France)
>
>
>
>
>
>
>
> From: MS_Access_Professionals@yahoogroups.com
> [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Luis
> Sent: Friday, March 29, 2013 4:16 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Multilevel BOM explosion
>
>
>
>
>
> Hi!
>
> Hope someone can help me out, I'll really appreciate it.
>
> I have an access table containing 3 fields with bill of material data.
>
> The content of just one product looks like this:
>
> field_Product, field_Component, field_Qty
> FP-5026-19-CHO, UP-5026-19-CHO, 1
> UP-5026-19-CHO, WS-5026-19 , 1
> WS-5026-19 , RM1 , 23
> WS-5026-19 , RM2 , 11
> WS-5026-19 , RM3 , 1.5
> UP-5026-19-CHO, FF-5026-19 , 1
> FF-5026-19 , RM4 , 23
> FF-5026-19 , RM5 , 0.456
> UP-5026-19-CHO, ST-5026-19 , 1
> ST-5026-19 , RM6 , 23
> ST-5026-19 , CT-5026-19-CHO, 1
> CT-5026-19-CHO, RM7 , 14
> CT-5026-19-CHO, RM8 , 14
>
> I need to output a list looking like this, without the subassembly parts.
>
> FP-5026-19-CHO, RM1 , 23
> FP-5026-19-CHO, RM2 , 11
> FP-5026-19-CHO, RM3 , 1.5
> FP-5026-19-CHO, RM4 , 23
> FP-5026-19-CHO, RM5 , 0.456
> FP-5026-19-CHO, RM6 , 23
> FP-5026-19-CHO, RM7 , 14
> FP-5026-19-CHO, RM8 , 14
>
> The table contains similar data for many more products.
>
> I have been trying nested queries but have not been able to get the result I
> need.
>
> Any ideas?
>
>
>
>
>
> [Non-text portions of this message have been removed]
>