Need for a recursive query

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?

Re: Need for a recursive query

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

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

Re: Need for a recursive query

On Apr 15, 3:20=A0am, Tom van Stiphout wrote:
> On Mon, 14 Apr 2008 08:12:31 -0700 (PDT), Jim Devenish
>
> wrote:
>
> 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 =3D StockItemAssemblies.StockItemID
> >Group By StockItemAssemblies.AssemblyID
>
> >This works fine. =A0Elsewhere 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.