Calculate price from hierarchical Bill of Materials 1

[LouiseJ]

Hi

I have the following

tblProduct
ProductID PK
ProductDescription

tblConponent
ProductID PK
ParentProductID FK
Quantity

tblPrice
PriceListNameID PK
ProductID PK FK
CostPrice
PercentageMarkUp


Essentially as I understand it this is a Bill of Materials

A product/component may form part of the assembly of more that one other product/component.

Only products/components that are not assembled (ie there are no components below them in the hierarchy) have records in tblPrice

I am trying to calculate a price for any product/component. This could be at any point in the hierarchy. I'm not entirely sure that this can be done. I have Googled and researched this including Joe Celko nested tables and BOM's, however I'm afraid his SQL is way beyond my comprehension. Currently the maximum number of levels in the hierarchy of the tree that a product / component has is 5 although many have fewer that this.

Is it possible to calculate these prices and if not what is the best way to do this?

Many thanks

LouiseJ

 

[SkipVought]

Hi,

When calculating cost in an indented BOM, you not only have Material Cost, but also Labor and overhead cost that must be calculated for each part at each level of the BOM using either actual hours or standard hours. Sometimes a part fabricated under a certain contract may have different factors.

It amy be helpful to start at the bottom indenture of the BOM and work upward, calculating and storing the material and touch labor costs.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[LouiseJ]

Thanks Skip

I have allowed for labour costs by adding labour as a component in the products table, its unit being 1 hour, and the labour hourly rate as a record in the price table. If an assembly takes two hours to assemble then two labour components are required as part of that assembly. Overheads are allowed for in the PercentageMarkUp field of the price table.

You suggest that to calculate the total price of a product ‘it would be helpful to start at the bottom indenture of the BOM and work upward’. In SQL how would I do that?

Thanks

LouiseJ

 

[MajP]

Sorry. I did not see this earlier. I find nested queries beyond me, but this is done very easily with a recursive call.

This concept is very useable dealing with any self referencing tables. Works for loading tree views.

Assume this is my table.

[tt]
compID parentCompID cost prodID quantity
A $5.00 ProductA 1
AB A $10.00 ProductA 3
ABA AB $1.00 ProductA 2
AC A $25.00 ProductA 5
ACA AC $5.00 ProductA 2
ACAA ACA $2.00 ProductA 3
ACB AC $1.00 ProductA 2
[/tt]

functions

Public Function getCost(compID As Variant) As Currency
  Dim rs As DAO.Recordset
  Dim strSql As String
  strSql = "Select * from tblComponents where compID = '" & compID & "'"
  Set rs = CurrentDb.OpenRecordset(strSql)
  
  getCost = getBranchCost(compID, rs!Cost * rs!quantity)
End Function

Public Function getBranchCost(parentID As Variant, Optional ByVal tempCost As Currency) As Currency
  Dim currentID As String
  Dim rs As DAO.Recordset
  Dim strSql As String

  strSql = "Select * from tblComponents where parentCompID = '" & parentID & "'order by compID"
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)

  Do While Not rs.EOF
    currentID = rs!compID
    getBranchCost = getBranchCost + rs!Cost * rs!quantity
    'Debug.Print "cost = " & getBranchCost & " current ID " & currentID
    getBranchCost = getBranchCost(currentID, getBranchCost)
    rs.MoveNext
  Loop
    getBranchCost = tempCost + getBranchCost
End Function

verification

?getCost("A")
 185 
?getCost("AC")
 148 
?getCost("ACA")
 21 
?getCost("AB")
 32 
?getCost("ABA")
 2

 

[LouiseJ]

Thanks MajP, your help is appreciated, although it will take me while to work my way through this code before I understand what it is doing!

Thanks

LouiseJ

 

[SkipVought]

MajP, I salute you! ==> [purple]*[/purple]

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]