I have a table named StockItems with field:
StockItemID
QuantityInStock
I am creating assemblies of stock items and have another table named
StockItemAssemblies with fields:
StockItemAssemblyID
AssemblyID
StockItemID
Quantity
I need to work out how many assemblies I have in stock from knowing
how many individual items are in stock and have the following query:
Select StockItemAssemblies.AssemblyID, Min(StockItems.QuantityInStock/
StockItemAssemblies.Quantity) As QuantityInStock
From StockItems Inner Join StockItemAssemblies On
StockItems.StockItemID = StockItemAssemblies.StockItemID
Group By StockItemAssemblies.AssemblyID
This works fine. Elsewhere in the program the QuantityInStock from
this query is used in place of the QuantityInStock in the StockItems
table when the item is an assembly. For an assembly the
QuantityInStock in the StockItems table is thus ignored.
However a component of an assembly could be another assembly.
How do I modify the query to properly include all sub-assemblies?
