these darn calculations??

[alberto]

hope someone out there can help.... writing an access database (invoices
system for a small company I run) and have a printable invoice in 'forms'
(which doubles up as a parcel label & delivery note) and managed to
succesfully put together calculations using the control source / expression
builder (ie 'product 'a' multiplied by selling price gives a total of 'b''
etc )... however the database table is pretty useless as these calculation
totals do not appear in the table master (only as zeros)... thus rendering
reports / queries also useless... any ideas?? at the moment the form shows
the calculation to
be correct but the 'table' shows the calcs a zeros....

 

[Rick B]

Please stick to one post. This question has been answered by an Access MVP
in one of the other newsgroups to which you posted the same issue.

 

[Allen Browne]

Alberto, the *only* sensible solution is to remove these bad fields from
your table.

As you found, it is easy and efficient to calculate the values in a query.
Use a query with the calculated fields as the source for your report. Why
would you ever want the trouble of keeping stored totals up date date, and
the worry that it might still not be right, when the calculated fields are
so easy and 100% reliable 100% of the time?

 

[alberto]

hi allen

thanks for your reply..... the only problem is I have made the calculation
in a customer 'form' so I can view (say) the total customer record and
invoice total**... I would like then to create a query / report of all the
customer totals** ... to be honest I have not created the calc in a query and
finding it diffuclt to do so... surely the master 'table' holds all the info
for use in various forms / queries / reports etc...

 

[Ken Sheridan]

You should not store the results of the calculations in the table. Its
what's known in the jargon as redundancy and leaves the door open to (more
jargon) update anomalies. Instead base your report on a query which includes
computed columns to do the calculations, e.g.

SELECT OrderNumber, Customer, UnitPrice * Quantity AS TotalPrice
FROM Orders;

To add a computed column to a query in design view rather than SQL as above
put the following in the 'field' row of a blank column in the design grid for
your query and make sure the 'show' checkbox is checked:

TotalPrice: [UnitPrice] * [Quantity]

 

[Allen Browne]

Well, you've heard enough voices consistently telling you that storing the
result of the calculation is wrong (breaks basic normalization rules), so
you probably realize that by now.

It's dead easy to calculate in a query. If you have not discovered the Total
button on the toolbar in query design (the icon is an upper case sigma),
that makes it easy to group by the customer and sum the amount. You can feed
your report from that query.