Bill of Materials & Price List

[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

I have the primary key in tblPrice on two fields to prevent a product be entered into the same price list twice.

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

Only products/components that are not assembled (ie they are at the bottom of the tree) have records in tblPrice

I am trying to calculate a price for any product/component that may be sold. This could be at any point in the tree. 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.

Before continuing to research this further I would be grateful if anybody could tell me whether what I am trying to achieve is even possible in Access. If not could anyone suggest a design or point me in the right direction.

Many thanks

LouiseJ

 

[johnherman]

Why not this?

Component Table
ComponentID
Description
Cost (or Price)
PercentMarkup

Assembly Table
AssemblyID
Description

AssemblyComponents Table (a bridge, or relation table)
AssemblyID
ComponentID
ComponentQuantity



-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[LouiseJ]

Thanks johnherman for the reply.

Whilst your suggested design will allow a price to be calculated I can not quite see how it allows an assembly to be assembled from other component and/or other assemblies.

Am I missing something?

LouiseJ

 

[johnherman]

It may not yet be optimized, but consider these members of the AssemblyComponent table

Corvette
Tire, 17 inch
4

Corvette
Fender, Rt Front
1

Corvette
Engine, 6.0 litre
1

Trailblazer L
Tire, 17 inch
4

Trailblazer L
Engine, 6.0 litre
1

Trailblazer X
Engine, 8.0 litre
1


-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[johnherman]

Component Table
OK, here it is:

ComponentID
Description
Cost (or Price)
PercentMarkup

Assembly Table
AssemblyID
Description

AssemblyComponents Table (a bridge, or relation table)
AssemblyID
ComponentID
ComponentQuantity

ProductComponents Table
ProductID
ComponentID
Component Quantity

ProductAssembly Table
ProductID
AssemblyID
Assembly Quantity

Product Table
ProductID
Description

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[LouiseJ]

Thanks Johnherman

I think I may not be explaining myself very well.

By introducing Product Tables as you suggest we have effectively added another level. A product can now be made up of a component or assembly. However it can not be made up from other products. To do that we could add a supper products table and then if required a super super products table and so on.

The original design with a ParentProductID in TblComponent allows for products to be made up from as many sub levels of assemblies as required. In the situation I am modelling some of the final products (products that are being sold) have five levels of assembly below them, ie the product is made up from multiple assemblies. Each of these assemblies can be made from multiple assemblies which in turn …… etc, etc, to five levels.

Given that we only have prices for the base components (the raw materials or components that are not formed from assemblies) what I can’t figure out is how to calculate prices for any other components/products that may be sold.

Any Ideas

Thanks

LouiseJ

 

[johnherman]

So what you're saying is that the assembly table is superfluous and there are only components (which can consist of other components) and products. Is that correct?

-------------------------
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[LouiseJ]

Thanks Johnherman. Apologies for the delay in replying, I’ve been away!

I have gone back over your previous reply and now understand what you are suggesting! Thank you. As for calculating a price for any product/component I think I should have posted that question over in the Queries and JET SQL Forum.