calculations and tables

  • Thread starter Thread starter Eric
  • Start date Start date
E

Eric

I'm relatively new to Access, and I'm in the process of
creating a few simple databases to manage our inventory.
This database is going to involve the usual entries
of "quantity" "cost each" and "total cost", of course. It
would be simplest if the "total cost" text box just
calculated itself once the user enters the qty and cost
each. But to do this, I have to replace the table field
name ("total cost") with the expression =[qty]*[cost each]
as the control source. When I do this, the calculation
happens automatically, but my table does not show the any
values for "total cost" because the form is no longer
saying that "total cost" is the control source. How can I
get my form to automatically calculate the total cost but
still have this value appear in the table that lists the
information entered on the form?

Thanks for the help.
 
Eric said:
I'm relatively new to Access, and I'm in the process of
creating a few simple databases to manage our inventory.
This database is going to involve the usual entries
of "quantity" "cost each" and "total cost", of course. It
would be simplest if the "total cost" text box just
calculated itself once the user enters the qty and cost
each. But to do this, I have to replace the table field
name ("total cost") with the expression =[qty]*[cost each]
as the control source. When I do this, the calculation
happens automatically, but my table does not show the any
values for "total cost" because the form is no longer
saying that "total cost" is the control source. How can I
get my form to automatically calculate the total cost but
still have this value appear in the table that lists the
information entered on the form?
Click to expand...

You don't. It is bad design to store calculated values. Create a query that is
identical to your table except that it includes an expression for doing the
calculation for "Total Cost". Then use the query every place you would ordinarily
have used your table.
 
I have to replace the table field
name ("total cost") with the expression =[qty]*[cost each]
as the control source. When I do this, the calculation
happens automatically, but my table does not show the any
values for "total cost" because the form is no longer
saying that "total cost" is the control source. How can I
get my form to automatically calculate the total cost but
still have this value appear in the table that lists the
information entered on the form?
Click to expand...

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.
 
Back
Top