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
|
|||
|
|||
Auto sum / total 2 fields in a record
Auto sum 2 fields in a record
Hi. This is incredibly easy to do in Excel but I’m afraid it’s eluding and frustrating me in Access!!! I have a very simple table with 5 columns formatted to collect money amounts. So, the User would enter the 5 money amounts into each record. So far so good…. The first 2 fields in each record collect “Income from farm”, the second field collects “Income from shop”. I’d like a 3rd field to automatically add up the content of what’s been entered in the first 2 fields (the 3rd field should never accept user input directly) and display that result in the record so the User can easily see the result. There are other subsequent fields in the record that would continue to accept manually entered money amounts. I’ve researched this a little and the solutions become incredibly complicated and often talk about Queries and or linking to Excel (ironically!!). I’m hoping for a very simple solution to a very simple problem. Hope you can help -- Peter |
#2
|
|||
|
|||
Auto sum / total 2 fields in a record
Peter,
In my opinion, you do not actually want to store that sum in the table. Storing values that are calculations based on other fields can cause numerous issues. For example, if someone updates the "Income from farm", your sum field would NOT automatically update. It becomes very easy to get bad data. In Excel it doesn't matter, since you actually storing a formula in a cell, but databases do not work like that. That being said, you can still display this sum on forms / reports / queries whenever you would like to. You can have an unbound textbox on a form that is set to be the sum of the 2 values, and similarly setup controls in reports/queries to show the sum. i.e. for a textbox you can set the Control Source to something similar to: =[Income from Farm] + [Income from shop] hope that make sense. |
#3
|
|||
|
|||
Auto sum / total 2 fields in a record
On Tue, 9 Mar 2010 10:24:07 -0800, Peter
wrote: Auto sum 2 fields in a record Hi. This is incredibly easy to do in Excel but Im afraid its eluding and frustrating me in Access!!! I have a very simple table with 5 columns formatted to collect money amounts. So, the User would enter the 5 money amounts into each record. So far so good. The first 2 fields in each record collect Income from farm, the second field collects Income from shop. Id like a 3rd field to automatically add up the content of whats been entered in the first 2 fields (the 3rd field should never accept user input directly) and display that result in the record so the User can easily see the result. There are other subsequent fields in the record that would continue to accept manually entered money amounts. Ive researched this a little and the solutions become incredibly complicated and often talk about Queries and or linking to Excel (ironically!!). Im hoping for a very simple solution to a very simple problem. Hope you can help Excel is a spreadsheet, best of breed. Access is a relational database programming environment. THEY ARE DIFFERENT!!!!! A table may look like a spreadsheet but it emphatically is NOT. The sum should be calculated dynamically, on the fly, *in a Query*, or in the control source of a form or report textbox. It should simply not exist in your table. For that matter, if you are storing two kinds of income... someday might you need three? or four? "Income from computer software business", "Income from farm implement repairs"...? Sure, in a spreadsheet you would just add more columns; but in a relational database you would have a *second table*, with one row per income stream. Access is very capable of doing what you want, but NOT in the way that you're trying to do it! Stop trying to use Access as if it were "Excel on Steroids", because that will just lead to vast frustration. Instead, do a little study on "normalization" and "relational database design", and work *with* Access instead of struggling against it. Here are some resources to help you do so. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Auto sum / total 2 fields in a record
Wow! I can't thank you enough. I've taken your comments on board AND
implemented the function - no surprise to you that it works. You've made me very happy and more educated. Very many thanks indeed. -- Peter "ghetto_banjo" wrote: Peter, In my opinion, you do not actually want to store that sum in the table. Storing values that are calculations based on other fields can cause numerous issues. For example, if someone updates the "Income from farm", your sum field would NOT automatically update. It becomes very easy to get bad data. In Excel it doesn't matter, since you actually storing a formula in a cell, but databases do not work like that. That being said, you can still display this sum on forms / reports / queries whenever you would like to. You can have an unbound textbox on a form that is set to be the sum of the 2 values, and similarly setup controls in reports/queries to show the sum. i.e. for a textbox you can set the Control Source to something similar to: =[Income from Farm] + [Income from shop] hope that make sense. . |
#5
|
|||
|
|||
Auto sum / total 2 fields in a record
Hello John. Yes, I’d forgotten my roots!! Too long ago to remember when
doing my Computer Science degree I once understood normalisation, DB design etc…. since then in the real World I forgot the basics and used Excel – perhaps too much. I do need to break the link and study again. Thanks for the injection of realism – seriously appreciated. -- Peter "John W. Vinson" wrote: On Tue, 9 Mar 2010 10:24:07 -0800, Peter wrote: Auto sum 2 fields in a record Hi. This is incredibly easy to do in Excel but I’m afraid it’s eluding and frustrating me in Access!!! I have a very simple table with 5 columns formatted to collect money amounts. So, the User would enter the 5 money amounts into each record. So far so good…. The first 2 fields in each record collect “Income from farm”, the second field collects “Income from shop”. I’d like a 3rd field to automatically add up the content of what’s been entered in the first 2 fields (the 3rd field should never accept user input directly) and display that result in the record so the User can easily see the result. There are other subsequent fields in the record that would continue to accept manually entered money amounts. I’ve researched this a little and the solutions become incredibly complicated and often talk about Queries and or linking to Excel (ironically!!). I’m hoping for a very simple solution to a very simple problem. Hope you can help Excel is a spreadsheet, best of breed. Access is a relational database programming environment. THEY ARE DIFFERENT!!!!! A table may look like a spreadsheet but it emphatically is NOT. The sum should be calculated dynamically, on the fly, *in a Query*, or in the control source of a form or report textbox. It should simply not exist in your table. For that matter, if you are storing two kinds of income... someday might you need three? or four? "Income from computer software business", "Income from farm implement repairs"...? Sure, in a spreadsheet you would just add more columns; but in a relational database you would have a *second table*, with one row per income stream. Access is very capable of doing what you want, but NOT in the way that you're trying to do it! Stop trying to use Access as if it were "Excel on Steroids", because that will just lead to vast frustration. Instead, do a little study on "normalization" and "relational database design", and work *with* Access instead of struggling against it. Here are some resources to help you do so. Jeff Conrad's resources page: http://www.accessmvp.com/JConrad/acc...resources.html The Access Web resources page: http://www.mvps.org/access/resources/index.html Roger Carlson's tutorials, samples and tips: http://www.rogersaccesslibrary.com/ A free tutorial written by Crystal: http://allenbrowne.com/casu-22.html A video how-to series by Crystal: http://www.YouTube.com/user/LearnAccessByCrystal MVP Allen Browne's tutorials: http://allenbrowne.com/links.html#Tutorials -- John W. Vinson [MVP] . |
Thread Tools | |
Display Modes | |
|
|