Using value from calculated field

  • Thread starter Thread starter krantzro
  • Start date Start date
K

krantzro

I have a form "Transport orders" which include a subform "Order Details". In
the subform I have a calculated field of the transport value for each
order(supplier). In fact, the total value of transport, for a transport
order, let's say X, is splitted in proportional values (based on each
supplier order's value) in X1, X2, ...
I need to have somehow these calculated values X1,X2, etc, in order to can
summarize them according to each supplier (maybe in a pivot table)
 
Could you build a query, and included the calculated fields there?

If so, you could then make it a Totals query (depress the Total button on
the toolbar in query design view), which will let you GROUP BY whatever
fields you want, and sum the calculated values.

Ultimately, the query could become the source for a report or something.
 
This is my problem ;).
I don't know how to build this querry. If I select in a querry the fields
from the subform, for each line of order detail/supplier I receive the same
value ( total transport value).
If I try to build an expression in a querry based on the calculated field,
Access asked to Enter parameter value for this field and I don't know what to
do.

"Allen Browne" a scris:
 
Post the expression from the text box on the form, that works.
From this expression, tell us whether each item is a text box or a field in
your table.

Ultimately, you are trying to create an expression in the query, without
referring to the form.
 
The expression for the calculated field [Text55] is:
[Order Value]/[Text14]*Forms![Transport Orders]![Transport Value]

In this expression, [Text14] is also a calculated field (in form footer) =
sum([Order Value])

[Transport Value] and [Order Value] are fields in 2 related tables.


The expression in the querry is :
Expr1: [Forms]![Legatura Subform]![Text55]
 
So the expression you will need to type into the Field row in query design
will be something like this:

[Order Value]
/ (SELECT Sum([Order Value]) AS SumOfOrderValue
FROM ]Table2] AS Dupe
WHERE Dupe.[OrderID] = Orders.[OrderID])
* (SELECT Sum([Transport Value]) AS SumOfTransportValue
FROM [Table3] AS Dupe
WHERE Dupe.[OrderID] = Orders.[OrderID])

This example assumes Table2 and Table3 are related to your Orders table by
the OrderID field.

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

krantzro said:
The expression for the calculated field [Text55] is:
[Order Value]/[Text14]*Forms![Transport Orders]![Transport Value]

In this expression, [Text14] is also a calculated field (in form footer) =
sum([Order Value])

[Transport Value] and [Order Value] are fields in 2 related tables.


The expression in the querry is :
Expr1: [Forms]![Legatura Subform]![Text55]

Allen Browne said:
Post the expression from the text box on the form, that works.
From this expression, tell us whether each item is a text box or a field
in
your table.

Ultimately, you are trying to create an expression in the query, without
referring to the form.
Click to expand...
Click to expand...
 
Thank you Allen, but it's still not working... I do something wrong...I am
just a beginner. Can I email you my database to try solving my request?
 
Please don't email the database.

We can make suggestions, but we can't pull everyone's databases apart and
fix them all.

(Thanks for asking rather than just doing it.)
 
Back
Top