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]
| Reply via web post | Reply to sender | Reply to group | Start a New Topic | Messages in this topic (2) |
Tidak ada komentar:
Posting Komentar