Form calculations on imported data

[Sara]

I recently imported 150 records into a table called Costs.

I have a form that displays the contents of Costs. In the form I have
a field where after inputting data (After Update event procedure), a
calculation is performed, the result is displayed in another field,
and the displayed result is recorded in the Costs table.

The records I imported already contain the data that would normally be
inputted directly into the form, after which the calculation would run
and display in another field, etc. Is there a way I can tell Access
to perform the calculations on the data that I imported without having
to re-enter the data as I tab through the form in order to get the
fields to calculate?

Thanks in advance.

 

[Ken Sheridan]

You don't need to store the result of the calculation in the table. Moreover
you should not do so as it introduces redundancy, which is not only
inefficient but, more importantly, leaves the door open to inconsistent data
being entered as there is nothing to stop the result of the calculation being
changed so that it no longer reflects the values from which it is derived, or
vice versa.

Instead of showing the result of the calculation in a bound text box, use an
unbound text box with the expression to calculate the result as the
ControlSource property of the text box. Say, for example, the two fields
being imported are Cost and Discount, the latter being expressed as a decimal
value e.g. 0.1 for 10 per cent, then the ControlSource to calculate the net
cost less discount would be:

=[Cost] – ([Cost]*[Discount])

The calculated net cost will automatically show in the text box and will be
updated if either the Cost or Discount values are changed.

You can do the same in a report, or in a query where you'd enter something
like this in the 'field' row of a blank column in query design view:

NetCost:[Cost] – ([Cost]*[Discount])

Ken Sheridan
Stafford, England

 

[Wolfgang Kais]

Hello Sara.

I recently imported 150 records into a table called Costs.

I have a form that displays the contents of Costs. In the form
I have a field where after inputting data (After Update event
procedure), a calculation is performed, the result is displayed
in another field, and the displayed result is recorded in the
Costs table.

The records I imported already contain the data that would normally
be inputted directly into the form, after which the calculation
would run and display in another field, etc. Is there a way I can
tell Access to perform the calculations on the data that I imported
without having to re-enter the data as I tab through the form in
order to get the fields to calculate?

Yes, you can let Access perform these calculations in an update query.
Create a new query based on the Cost table. Change the query type to
update query (the query menu, if not using Access 2007). Add the field
that will store the calculated value, add criteria for that field to
identify only those records that need the calculation (probably
IS NULL). In the update row in the design grid, enter the formula
based on the other fields of the table. After executing the query
(use the exclamation mark in teh toolbar), the field will contain
the calculated value.