calculations in form does not show in table

[Guest]

I have tried everything that I can humanly think of. I have created an access
form that has calculations in it. When I check to see if the table is
updating as well, there are no calculations being done. There is a need for
these calculations, in order to make reports, and they will not show up in
the reports because they do not show up in the table. The form is working out
perfectly. So please, someone hopefully will have the answer for this
problem, and I won't have to dream about it anymore.

Thanks

 

[Guest]

Generally speaking values calculated from data in other columns in a table
should not be stored in the table; it constitutes redundancy and leaves the
table at risk of update anomalies. You can either do the calculations in
unbound controls in the report in exactly the same way as you have in the
form, or you can do them in a query and base the report on the query.

Say you have columns UnitPrice, TaxRate (as a decimal value, e.g. 0.175 for
17.5 percent) and Quantity in an OrderItems table and you want to compute the
gross price, then in a report the ControlSource of an unbound text box would
be, just as in a form:

=[UnitPrice] * [Quantity] * (1 + [TaxRate])

If you want to sum the gross price values in a group or report footer you
repeat the expression as you cannot Sum a computed control:

=Sum([UnitPrice] * [Quantity] * (1 + [TaxRate]))

In a query you'd use a similar expression, e.g. in the 'field' row of a
blank column in query design grid:

GrossPrice:[UnitPrice] * [Quantity] * (1 + [TaxRate])

In the report you can then bind a control to the GrossPrice column, and in
this case you can sum it directly in a footer:

=Sum([GrossPrice])

Ken Sheridan
Stafford, England

 

[John W. Vinson]

I have tried everything that I can humanly think of. I have created an access
form that has calculations in it. When I check to see if the table is
updating as well, there are no calculations being done. There is a need for
these calculations, in order to make reports, and they will not show up in
the reports because they do not show up in the table. The form is working out
perfectly. So please, someone hopefully will have the answer for this
problem, and I won't have to dream about it anymore.

Thanks

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

If you're assuming that the value must be stored in a table in
order to appear in a report - revise your assumption; it's wrong.

John W. Vinson [MVP]