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

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.

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]

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.
.

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]
.