Product A = Product B + Product C + ....... 
Author Message
 Product A = Product B + Product C + .......

Hi, all interested,
An ALL-QBE-ACCESS solution was no so hard, after all.
Just create, in module Example1, (CPMPERT.mdb) the following function:
Public Function ItTakes_2(ProdID As Long, HowMany As Long, SubProd As Long)
As Long
Dim Qty As Long
Dim AlreadyReq As Long

    On Error Resume Next    ' Test to see if ProdID exists in the collection
        Qty = C.Item(Prefix & ProdID)
        If 0 <> Err.Number Then
            ItTakes_2 = 0
            Exit Function   ' Not a required sub-product, get out
            ItTakes_2 = Qty
        End If
    On Error GoTo 0

    ' Logic particular to "make a product from sub-products"
    '   Qty== how many of this sub product is required
    '   SubProd == a subproduct required to make it
    '   HowMany == howMany SubProd is requires to make one (1) ProdID
    '   So, we need, Qty*HowMany of product type SubProd. Simple.

    On Error Resume Next
        AlreadyReq = C.Item(Prefix & SubProd)
        If Err.Number <> 0 Then
            On Error GoTo 0 ' Have-I ever said I hate error trapping
implementation in VB?
            ' First time this prod is required, so
            C.Add Qty * HowMany, "Prod" & SubProd
            On Error GoTo 0
            ' we already need AlreadyReq times this prod,
            ' so AlreadyReq+Qty*HowMany is the new qty.
            ' Unfortunately, no way to "assign" a item in a collection, so
            ' drop it, and recreate it!
            C.Remove Prefix & SubProd
            C.Add AlreadyReq + Qty * HowMany, "Prod" & SubProd
        End If
    On Error GoTo 0
End Function
Then, change Query1, or make a new one, with the following statement:
SELECT Table1.ProdID, Last(Table1.MadeOf) AS LastOfMadeOf,
Last(Table1.SubProdID) AS LastOfSubProdID,
Last(ItTakes_2([ProdID],[MadeOf],[SubProdID])) AS TimesRequired
FROM Table1
GROUP BY Table1.ProdID
HAVING (((Last(ItTakes_2([ProdID],[MadeOf],[SubProdID])))>0));
And finally, make field Node1 of Table1 a primary index, order: Ascending.
How it is working, now. Just as before, may be simpler:
(Set C = Nothing ' if you already have make a run-test)
MakeProduct 1  ' From Debug window, since you wish to "make" productID=  1

Open the modified Query1, that's all.
Note that is you only wish the "atomic" parts, not the sub-assemblies (the
"path"), only filter for the not zero "LastOfMadeOf" field (be carefull to
run that query only ONCE: If required to re-run it, re-initialize the
process.) It is "simpler" since you "see" the results in the query. Note
that the result is also in the collection, as before.
That's it.
Hope it may help,
Vanderghast, Access MVP.


>The story of how to make a product, A, given the required components,
>themselves splitted in sub and sub-sub-components is not obvious in SQL
>since the venerable language don't allow recursion. On the other hand, with
>Access, this is POSSIBLE to solve that kind of problems in ONE SINGLE QUERY

>I have include an attachement for everyone interested (and judging with the
>number of question close to that kind of problem..... 48K dosn't seem too

>Hope it may help,
>Vanderghast, Access MVP

Mon, 14 Aug 2000 03:00:00 GMT  
 [ 1 post ] 

 Relevant Pages 

1. Product ID, Product Name

2. New Product For Easily Building Web Applications From Access

3. new type of aggregate function (product function)

4. New Product: Total Visual SourceBook for Access and VB

5. the best products on the net 5424

6. Assign Products to Categories via a Module

7. Product or Geomean functions in Access

8. FMS Inc. products

9. Product Inquiry: Access/Web based Document repository...please recommend

10. Product registration info in splash screen

11. Is there a better product out there?

12. how do I get around the expration of my product


Powered by phpBB® Forum Software