Need for a recursive query

[Jim Devenish]

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?

[Tom van Stiphout]

On Mon, 14 Apr 2008 08:12:31 -0700 (PDT), Jim Devenish
<in***************@foobox.comwrote:

There is no support for recursive queries in Access SQL. You can put
the same table on a query more than once (once for parent, once for
child, once for grandchild etc), but that way you can only query
exactly N levels deep.

A few options I can think of:
* Write it using recursive VBA/DAO code.
* Port your database to sqlserver 2005; it has native (and elegant)
support for recursive queries.
* Use nested sets
(http://www.intelligententerprise.com/001020/celko.jhtml) which are
supported by any dbms.

-Tom.

>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?

[Jim Devenish]

On Apr 15, 3:20*am, Tom van Stiphout <[e-mail]:

On Mon, 14 Apr 2008 08:12:31 -0700 (PDT), Jim Devenish

<[e-mail]:

There is no support for recursive queries in Access SQL. You can put
the same table on a query more than once (once for parent, once for
child, once for grandchild etc), but that way you can only query
exactly N levels deep.

A few options I can think of:
* Write it using recursive VBA/DAO code.
* Port your database to sqlserver 2005; it has native (and elegant)
support for recursive queries.
* Use nested sets
(http://www.intelligententerprise.com/001020/celko.jhtml) which are
supported by any dbms.

-Tom.

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?

Thanks Tom. It is helpful to learn that it cannot be done in Access
SQL. It will stop me beating myself up. I will do it in VBA then.