By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,587 Members | 1,168 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,587 IT Pros & Developers. It's quick & easy.

Need for a recursive query

P: n/a
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?
Jun 27 '08 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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?
Jun 27 '08 #2

P: n/a
On Apr 15, 3:20*am, Tom van Stiphout <no.spam.tom7...@cox.netwrote:
On Mon, 14 Apr 2008 08:12:31 -0700 (PDT), Jim Devenish

<internet.shopp...@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?
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.
Jun 27 '08 #3

This discussion thread is closed

Replies have been disabled for this discussion.