If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum and product function in table
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? |
#2
|
|||
|
|||
Sum and product function in table
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 the 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). -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Sam" wrote in message news 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? |
#3
|
|||
|
|||
Sum and product function in table
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. -- Steve Schapel, Microsoft Access MVP Sam wrote: 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? |
#4
|
|||
|
|||
Sum and product function in table
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" wrote: 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 the 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). -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Sam" wrote in message news 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? |
#5
|
|||
|
|||
Sum and product function in table
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. -- Allen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Sam" wrote in message ... 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" wrote: 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 the 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). "Sam" wrote in message news 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? |
Thread Tools | |
Display Modes | |
|
|