Can't build right formula

[egidijus.jankauskas]

Hi,
could you please tell me how tu build Formula1 and Formula2 (look in picture). It's already third day when I am trying to do this, but ussally the reslut is the same " enter parameter value" or error, or nothing. Thank you in advance.

 

[Uncle Gizmo]

You want to sum those two columns?

 

[egidijus.jankauskas]

You want to sum those two columns?

yes, that's right. I want to sum those two columns.

 

[vbaInet]

Basically you can't Sum() calculated values in controls. If you put the calculation in the Record Source of the form, i.e. the query behind the form, then you can Sum on that alias field.

For example, in a new column in the query, for TCS you will put this:

TCS: [Percent] * [Transportation Costs]

In the Control Source of the TCS textbox select TCS.

Finally, you can now put =Sum([TCS]) in the footer to get a sum.

 

[egidijus.jankauskas]

Then appears another problem, how to group a data in Query (picture attached)? And group should be done by name and by Invoice No, that I could use these calculations in my report. Consequently 1 and 2 row should appear as sum as it is the same Product (Name) and the same invoice. How to summ [Weight] field separated by Invoices? I have done this in a Form, but I can't use calculated fields in form as well for my report.

 

[vbaInet]

Have a look at this link:

http://office.microsoft.com/en-us/access-help/sum-data-by-using-a-query-HA010096310.aspx

 

[egidijus.jankauskas]

So now I have two queries. One for separate records, another for totals. And if I want to calculate percentage of total [Weight] by additional record in field [POTC] I have to enter a formula "POTC: [Weight]/[Receiving Query1]![Sum Of Weight]" but the result is "Enter parameter value"

 

[vbaInet]

"POTC: [Weight]/[Receiving Query1]![Sum Of Weight]"

You can't reference a query like that.

Use a DLookup()

http://www.mvps.org/access/general/gen0018.htm
http://www.techonthenet.com/access/functions/domain/dlookup.php

 

[egidijus.jankauskas]

Thank you vbaInet IT seems that I succesfully have build a formula. But it is left one more unclear point. Please look at picture attached. Last row shows strange number 342,38 % when it shoul be aprox 85 %(179.750/210.633).
Formula is:
POTC: [Weight]/DLookUp("[Sum Of Weight]";"[Receiving Query1]")

 

[vbaInet]

You're missing the third part of the DLookup() function which will link it to that current record. E.g.:

DLookup("field", "table", "id = " & [id])