Sum and product function in table

[Sam]

I have 2 questions,
First, In the table of design view,
I have a table with 9 fields
productname1 picecs1 unitprice1 subtotal1
productname2 pieces2 unitprice2 subtotal2 Total Amount
how do I enter the expression in the field of subtotal1 to give me the
product of picecs1 and unitprice1 (same in the field of subtotal2)

Second, I would like to add subtotal1 and subtotal 2 to give me a total in
the Total Amount field, how do I enter the expression?

 

[Allen Browne]

Sam, your's is a very important question, as there is a really important
principle of database design here.

The simple answer is that you create the calculation in a query, not in a
table. For more detail, see:
Calculated fields
at:
http://allenbrowne.com/casu-14.html

After reading that, open the Northwind sample database that installs with
Access. Choose Relationships on the Tools menu, and look at how the tables
are related to each other. One Order can contain many Order Details (the
line items of the order.) This copes with any number of items in one order,
and is infinitely better than the structure you proposed.

Now look at the query named Order Details Extended. It illustrates how to
perform the calculation (the field named ExtendedPrice.) Do you see that the
chance of a wrong value being stored in the Order Details table is zero,
because no value is stored there: it is always calculated as needed, and can
never be wrong. Save you heaps of work if you do it that way.

Finally, you asked about how to get the total. There are two example of this
in Northwind:
1.) Open the Orders form in design view, and see how it picks up the
Subtotal from the subform. That's how you do it for forms.

2.) Open the Invoice form in design view. It is based on a query that gets
all the data from multiple tables, and it groups on the OrderID field
(Sorting and Grouping dialog on the View menu.) As a result, it sums the
ExtendedPrice field in the Order ID Footer section. That's how you do it for
reports.

Hopefully that will get you going with a maintenance-free database (no
calculated data stored to worry about).

 

[Steve Schapel]

Sam,

You can't do calculations in a table. That's not what tables are for -
they are for data storage.

Not only that, it is not at all a good idea to try to use one record in
your table to store information about more than one item.

It is difficult to advise explicitly, without knowing more details of
what your project is all about. But essentially you should have 2
tables. One of which has each record defining the single entity which
productname1 and productname2 both relate to. And the other, to replace
the existing table, with heve these fields...
entityID productname picecs unitprice
.... where the entry in the entityID field will indicate which record in
the first table this data is related to. That's how Access works. It
may seem more complicated at first glance, but I assure you it's
actually simpler and more efficient.

And then, your subtotals and totals are done in Queries.

 

[Sam]

Allen Browne,

Thank you.

Actually I have a query which is related to this table, I have created the
calculation in a query now. I just want to know that can I updated the table
after the calculation done in a query.

Sam

 

[Allen Browne]

The important thing is NOT to store the calculated value in the table. You
ALWAYS get it as a calculated query field, and then you never have to worry
if it was updated correctly or might be stored wrongly.

This principle is one of the basic rules of data normalization. If you want
to break the rules and store the data anyway, you should be aware of the
maintenance implications you are taking on board. There is an example in the
link I gave you of how to do it anyway, using the events of the form.