M
mav100
Hello all...
I'm currently building a database that will show pricing on items for
sale. The issue I have is that the items table (tblItems) contains all
items to be sold, from the lowest level subcomponent to the highest
level item sold that, when built, contains multiple subcomponents which
themselves contain subcomponents which themselves may contain
subcompnents again... You get the picture on that I hope! Since all
items are stored in the same table (which is needed in this database
for other reasons, as the subcomponents are also sold by themselves), I
created a table to relate subcomponents to higher level components,
called tblsubcomp_relations. This table shows the relationship of a
higher level component to a lower level component.
Example:
Widget 1 - highest level component
Widget 2 - subcomponent of Widget 1
Widget 3 - subcomponent of Widget 2
Widget 4 - subcomponent of Widget 3
and so on.... there are many multiple levels
the tblsubcomp_rel shows the following fields:
Primary Key; Component(looked up in tblItems); SubComp(also looked up
in tblItems); and NumSub(the number of this subcomponent required to
make the component).
Example of table:
Key Component SubComp NumSub
1 Widget 1 Widget 2 5
2 Widget 2 Widget 3 2
3 Widget 3 Widget 4 1
Here comes the dilemma. I need to create a query (or queries) that
will be able to accurately price a component along with adding in the
cost of the subcomponent (or subcomponents). In order to do this I need
to get costs from a separate table where a resource needed to build
that component is stored. Each item will use X amount of a resource,
and may or may not use a subcomponent. So I need to add the cost of
the resource to the cost of the subcomponents to get the total cost of
the item.
Example:
Widget 1 uses 10 of resource Y, and 10 of resource Z. Lets say the
cost per unit of each resource is = 1 to make the math simple for now.
So, the resource cost of Widget 1 is 20. To get an accurate cost for
Widget 1, I need to also include the cost of Widget 2 (resources +
(cost of Widget 3 time the number of Widget 3's needed).
Basically what I appear to have to do here is get a query to read
infomation from itself. Somehow I need the query to get the price of
Widget 4 from itself, and use it in the calculation fo Widget 3, and in
turn use the calculated cost of Widget 3 in the cost of Widget 2, and
then the calculated cost of Widget 2 in the cost of Widget 1. Some
items may only require 1 lower level component, and some may require 5
or 6 levels or more. Does anyone have any idea how to accomplish this?
I'm currently building a database that will show pricing on items for
sale. The issue I have is that the items table (tblItems) contains all
items to be sold, from the lowest level subcomponent to the highest
level item sold that, when built, contains multiple subcomponents which
themselves contain subcomponents which themselves may contain
subcompnents again... You get the picture on that I hope! Since all
items are stored in the same table (which is needed in this database
for other reasons, as the subcomponents are also sold by themselves), I
created a table to relate subcomponents to higher level components,
called tblsubcomp_relations. This table shows the relationship of a
higher level component to a lower level component.
Example:
Widget 1 - highest level component
Widget 2 - subcomponent of Widget 1
Widget 3 - subcomponent of Widget 2
Widget 4 - subcomponent of Widget 3
and so on.... there are many multiple levels
the tblsubcomp_rel shows the following fields:
Primary Key; Component(looked up in tblItems); SubComp(also looked up
in tblItems); and NumSub(the number of this subcomponent required to
make the component).
Example of table:
Key Component SubComp NumSub
1 Widget 1 Widget 2 5
2 Widget 2 Widget 3 2
3 Widget 3 Widget 4 1
Here comes the dilemma. I need to create a query (or queries) that
will be able to accurately price a component along with adding in the
cost of the subcomponent (or subcomponents). In order to do this I need
to get costs from a separate table where a resource needed to build
that component is stored. Each item will use X amount of a resource,
and may or may not use a subcomponent. So I need to add the cost of
the resource to the cost of the subcomponents to get the total cost of
the item.
Example:
Widget 1 uses 10 of resource Y, and 10 of resource Z. Lets say the
cost per unit of each resource is = 1 to make the math simple for now.
So, the resource cost of Widget 1 is 20. To get an accurate cost for
Widget 1, I need to also include the cost of Widget 2 (resources +
(cost of Widget 3 time the number of Widget 3's needed).
Basically what I appear to have to do here is get a query to read
infomation from itself. Somehow I need the query to get the price of
Widget 4 from itself, and use it in the calculation fo Widget 3, and in
turn use the calculated cost of Widget 3 in the cost of Widget 2, and
then the calculated cost of Widget 2 in the cost of Widget 1. Some
items may only require 1 lower level component, and some may require 5
or 6 levels or more. Does anyone have any idea how to accomplish this?