form based of recuresive query

[smig]

I have a form that is based on a query

The first query will calculate amount of material in product (AmountOfProduct * PercentOfMaterial)
The second will take the first one and calculate the price and weight. (Query1 * MaterialPrice, Query1 * MaterialWeight)

How can I do that ?

 

[arnelgp]

what is the Column Name for (AmountOfProduct * PercentOfMaterial) on first query?
use that in your second query:

select Query1.TheColumn * Product.MaterialPrice As Expr1, Query1.TheColumn & Product.MaterialWeight As Exp2
from product inner inner join Query1 on product.productCode = Query1.poductCode;

 

[smig]

what is the Column Name for (AmountOfProduct * PercentOfMaterial) on first query?
use that in your second query:

select Query1.TheColumn * Product.MaterialPrice As Expr1, Query1.TheColumn & Product.MaterialWeight As Exp2
from product inner inner join Query1 on product.productCode = Query1.poductCode;

this is the first part (Q1)

CalcQtyML: fnCalcQtyML([Products_Materials].[ProductID],ReplaceNOE([Forms]![Products_Form]![Products_SubForm].[Form]![ProductQty],0),[Products_Materials].[PercentOfFinalProduct],[Materials].[MaterialTypeID],[MaterialPercentOfProduct],[MaterialEvaporaionLevel],[EssentialOilMultiplier])

Next I want to calculate the price and weight based on the QtyML

 

[arnelgp]

your query will become "slower" as your table grows with more records.

 

[smig]

your query will become "slower" as your table grows with more records.

not sure why, as I calculate it for a specific Product (ProductID)
in any case I have no other option as I must do some calcs based on all data

I don't want to calc Q1 for all records

but my main question is how should my query looks like
O r should I make Q1 outside the record source ?

Thanks

 

[Minty]

The second query should look something like

Select Q1.CalcQtyML * MaterialPrice as Res1, Q1.CalcQtyML * MaterialWeight as Res2
From ProductTable inner join Query1 on ProductTable.productCode = Q1.poductCode;

Obviously guessing your table names a bit.