form based of recuresive query (1 Viewer)

smig

Registered User.
Local time
Today, 14:01
Joined
Nov 25, 2009
Messages
2,209
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:01
Joined
May 7, 2009
Messages
19,175
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

Registered User.
Local time
Today, 14:01
Joined
Nov 25, 2009
Messages
2,209
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

..forever waiting... waiting for jellybean!
Local time
Today, 20:01
Joined
May 7, 2009
Messages
19,175
your query will become "slower" as your table grows with more records.
 

smig

Registered User.
Local time
Today, 14:01
Joined
Nov 25, 2009
Messages
2,209
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
 
Last edited:

Minty

AWF VIP
Local time
Today, 12:01
Joined
Jul 26, 2013
Messages
10,355
The second query should look something like

Code:
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.
 

Users who are viewing this thread

Top Bottom