saving expressions

[EDEMAY]

I recently began setting up a database for a small
business. I developed an invoice form as well as an
invoice table. On the invoice form, there are some
expressions, for example to calculate subtotal, tax,
final balance, etc. My problem is that the calculated
values do not save to the invoice table where i plan to
store all the information. All other fields save fine
(i.e. Name, Address,etc...) It is important to keep it
all in one place for simplicity. Is there a simple
solution to this problem??? Thank You in adavance for any
help.

 

[Nikos Yannacopoulos]

One way to do it is to use a text box for each calculated amount in the
invoice entry form, have the on change event of each of the main input text
boxes (such as product code - which presumably pulls the unit price,
quantity, discount etc.) fire a macro or some code that does the
calculations and puts the results in the calculated amounts text boxes, and
bound the latter to the table fields.

A word on the design: Unless you never have more than one line item per
invoice, you should use a header table and a line item table, a form for the
header with a subform for the line items, do the calculations at line item
level and store calculated amounts in the line items table, and possibly sum
up and store totals in the header if required (unlikely).

HTH,
Nikos

 

[Wayne Morgan]

Yes, there is a simple solution, don't. It is bad practice and can cause
problems to store calculated data. Just calculate the information when
ever/where ever you need it. If the value can be calculated, you have all of
the "necessary" information. If not, you wouldn't be able to make the
calculation.

Now, that said, if you must store this data it can be done. Probably the
easiest way is to create a second textbox on the form for each of the
calculated textboxes. Set the Visible property for these duplicate textboxes
to No. Bind them to the appropriate fields. In the form's BeforeUpdate
event, set the value of each of these textboxes to match their associated,
calculated textbox.

If you are trying to do this because the tax rate may change or other such
items may change, it would be better to store the effective date of the tax
rate and what the rate is in a table and look up the appropriate tax rate
based on the order date than to store the resulting values.

 

[Nikos Yannacopoulos]

Wayne,

In principle I agree with your first and last paragraph 100%. Especially as
far as the first one goes, I myself have given the exact same advice on a
few occasions. I've had this question in the back of my mind for quite some
time, though: large ERP's like SAP and BCPS go the other way (i.e. they
store all calculated fields). Why do they do that? I can only speculate:

1. It is a lot more efficient in running all sorts of reports, and saves
greatly on processor time, which is often crucial for large scale multiuser
client-server systems

2. The additional data stored is peanuts compared to the total data stored
by an ERP (incredible duplication of data, you wouldn't believe it if you
haven't seen it), and the burden is absolutely offset by the saving in
run-time resourse consumption

3. Sub-optimal design (?)

In a nutshell, different priorities vs. a muc more limited scale Access
database application?

Any thoughts?

Regards,

Nikos

 

[Wayne Morgan]

Stephen Lebans has given some answers regarding this, I believe he has dealt
with some of these larger systems. He would probably be a better source to
answer these questions. I have not had any experience with systems such as
those you mention.