Need for a recursive query

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • Jim Devenish
    #1

    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
    StockItemAssemb lies with fields:
    StockItemAssemb lyID
    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 StockItemAssemb lies.AssemblyID , Min(StockItems. QuantityInStock/
    StockItemAssemb lies.Quantity) As QuantityInStock
    From StockItems Inner Join StockItemAssemb lies On
    StockItems.Stoc kItemID = StockItemAssemb lies.StockItemI D
    Group By StockItemAssemb lies.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?


    Tags: None
    • Tom van Stiphout
      #2
      Re: Need for a recursive query

      On Mon, 14 Apr 2008 08:12:31 -0700 (PDT), Jim Devenish
      <internet.shopp ing@foobox.comw rote:

      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
      >QuantityInStoc k
      >
      >I am creating assemblies of stock items and have another table named
      >StockItemAssem blies with fields:
      >StockItemAssem blyID
      >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 StockItemAssemb lies.AssemblyID , Min(StockItems. QuantityInStock/
      >StockItemAssem blies.Quantity) As QuantityInStock
      >From StockItems Inner Join StockItemAssemb lies On
      >StockItems.Sto ckItemID = StockItemAssemb lies.StockItemI D
      >Group By StockItemAssemb lies.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
      >QuantityInStoc k 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?
      >

        Comment

        • Jim Devenish
          #3
          Re: Need for a recursive query

          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.comw rote:
          >
          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
          StockItemAssemb lies with fields:
          StockItemAssemb lyID
          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 StockItemAssemb lies.AssemblyID , Min(StockItems. QuantityInStock/
          StockItemAssemb lies.Quantity) As QuantityInStock
          From StockItems Inner Join StockItemAssemb lies On
          StockItems.Stoc kItemID = StockItemAssemb lies.StockItemI D
          Group By StockItemAssemb lies.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.


            Comment

            Previous template Next
            Working...