Calculating Material Usage

[Nick Bradbury]

Hi

I need to summarise the amount of inventory used over a specified period, I
have a query that gathers this data but I need to calculate totals for each
inventory item not the summary used in each BOM. The problem is that each
inventory item is used in many Bill of Materials and not always in the same
Item within each BOM.

For Example

BOMID1 BOMID2
Item1 - PET001RO1 2 Item1 - DSR009R01 2
Item2 - PET002RO1 3 Item2 - PET001RO1 5
Item3 - PET003RO1 1 Item3 - PET004R01 10

I want to know the grand total of PET001RO1 but it is lited as item 1 in
BOM1 and Item2 in BOM2. When I created the BOM's I used Combo boxes in the
BOM table to look up the values in the StockItem table.

Is it possible to summerise the inventory the way I want or do I need to
change the relationship between the BOM items and the StockItems table.

Hope this makes sense.

Thanks

Nick

 

[kingston via AccessMonster.com]

If you mean that BOMID1 and BOMID2 are separate tables (in which case I think
you should reconsider your data structure), then you can combine them using
an SQL union query:

SELECT * FROM BOMID1
UNION SELECT * FROM BOMID2
UNION SELECT * FROM BOMID3
...

Then you can perform an overall summation for a specific part based on the
result of the union query. However, I have a feeling that this is not the
end of the story and you're really looking for a way to calculate usage in
nested BoMs.

 

[Klatuu]

This is not that hard to do. the question is where are you doing it?
(report, form?) The reason is, the solution may be different depending on
how you want to use it.
Hopefully, your BOM table will have the date of the BOM, the item number,
and the quantity. That will be all you need.

Post back with some particulars and I will help you come up with a plan.

 

[Nick Bradbury]

Hi guys

Thanks for your replies so far. To clarify the situation all the BOM's are
in the tblBOM each BOM is related to a particular TestSuite so my idea is to
calculate usage of materials by multiplying the number of times each
TestSuite is conducted in a period by the BOM used in each TestSuite. My
problem is that each BOM has up to 10 components in it and I cannot figure
out how to summarise the amount of each material is used in all the BOM's
when a component can be Item1 in one BOM and appear as Item2 in another BOM,
Item3 in another and so on.

Cheers

Nick

 

[Klatuu]

Do you not have the inventory product to part number in tblBOM that relates
to the item number? You really need to be able to capture that. The item
number is really irrelavent for this problem.

 

[Nick Bradbury]

Hi Klatuu

Thers is no direct relationship [at present] between the tblBOM and
tblStockItems, or the ItemNo in tblBOM and StockItemID in tblStockItems. I
have put the fields for each table below

tblBOM

TestSuiteBOMID - Autonumber [PK]
TestSuiteID - Number
Item1 - Text
Item1Qty - Number
Item2 - Text
Item2 - Number

etc up to 10 items, the Item field is using a combo box to select the
appropriate inventory item from the table tblStockItem. The fields for this
table are as follows

tblStockItems

StockItemID - Autonumber [PK]
MSLCatNo - Text
Category - Text
Description - Text

I am unable to figure out the type of relationship/s that I need to create
between the table or if it needs to be structured differently.

Nick

 

[Klatuu]

This is the problem. You really need to be able to associate stock items in
a BOM. Before you do anything else, you need to correct that problem. An
arbitrary item number is of no value other than to keep the rows in BOM in
order. A typical BOM structure has two tables, a header table and a detail
table. They are related by the BOM number or an Autonumber primary key
unique to the BOM number. What you are calling item number is usually
referred to as line number.

Hi Klatuu

Thers is no direct relationship [at present] between the tblBOM and
tblStockItems, or the ItemNo in tblBOM and StockItemID in tblStockItems. I
have put the fields for each table below

tblBOM

TestSuiteBOMID - Autonumber [PK]
TestSuiteID - Number
Item1 - Text
Item1Qty - Number
Item2 - Text
Item2 - Number

etc up to 10 items, the Item field is using a combo box to select the
appropriate inventory item from the table tblStockItem. The fields for this
table are as follows

tblStockItems

StockItemID - Autonumber [PK]
MSLCatNo - Text
Category - Text
Description - Text

I am unable to figure out the type of relationship/s that I need to create
between the table or if it needs to be structured differently.

Nick

Do you not have the inventory product to part number in tblBOM that
relates
to the item number? You really need to be able to capture that. The item
number is really irrelavent for this problem.

 

[Nick Bradbury]

Hi Klatuu

Problem solved, I was making this more complicated than it needed to be.

Thanks for your halp

Nick

This is the problem. You really need to be able to associate stock items
in
a BOM. Before you do anything else, you need to correct that problem. An
arbitrary item number is of no value other than to keep the rows in BOM in
order. A typical BOM structure has two tables, a header table and a
detail
table. They are related by the BOM number or an Autonumber primary key
unique to the BOM number. What you are calling item number is usually
referred to as line number.

Hi Klatuu

Thers is no direct relationship [at present] between the tblBOM and
tblStockItems, or the ItemNo in tblBOM and StockItemID in tblStockItems.
I
have put the fields for each table below

tblBOM

TestSuiteBOMID - Autonumber [PK]
TestSuiteID - Number
Item1 - Text
Item1Qty - Number
Item2 - Text
Item2 - Number

etc up to 10 items, the Item field is using a combo box to select the
appropriate inventory item from the table tblStockItem. The fields for
this
table are as follows

tblStockItems

StockItemID - Autonumber [PK]
MSLCatNo - Text
Category - Text
Description - Text

I am unable to figure out the type of relationship/s that I need to
create
between the table or if it needs to be structured differently.

Nick

Do you not have the inventory product to part number in tblBOM that
relates
to the item number? You really need to be able to capture that. The
item
number is really irrelavent for this problem.

:

Hi guys

Thanks for your replies so far. To clarify the situation all the BOM's
are
in the tblBOM each BOM is related to a particular TestSuite so my idea
is
to
calculate usage of materials by multiplying the number of times each
TestSuite is conducted in a period by the BOM used in each TestSuite.
My
problem is that each BOM has up to 10 components in it and I cannot
figure
out how to summarise the amount of each material is used in all the
BOM's
when a component can be Item1 in one BOM and appear as Item2 in
another
BOM,
Item3 in another and so on.

Cheers

Nick




Hi

I need to summarise the amount of inventory used over a specified
period,
I have a query that gathers this data but I need to calculate totals
for
each inventory item not the summary used in each BOM. The problem is
that
each inventory item is used in many Bill of Materials and not always
in
the same Item within each BOM.

For Example

BOMID1 BOMID2
Item1 - PET001RO1 2 Item1 - DSR009R01 2
Item2 - PET002RO1 3 Item2 - PET001RO1 5
Item3 - PET003RO1 1 Item3 - PET004R01 10

I want to know the grand total of PET001RO1 but it is lited as item
1
in
BOM1 and Item2 in BOM2. When I created the BOM's I used Combo boxes
in
the
BOM table to look up the values in the StockItem table.

Is it possible to summerise the inventory the way I want or do I
need
to
change the relationship between the BOM items and the StockItems
table.

Hope this makes sense.

Thanks

Nick