how to sum values in multiple fields and put in another

[Dgherboldt]

I have created a simple asset base in Access 2007 to record equipment, and
the number of each item held in various places (e.g. Item = PC, Loc A=10, Loc
B=6, Loc C=5). I want a field in my table to show the total held (e.g. 21). I
can do this in a report, but I want it in the table so it is visible in all
the forms. I've tried to create an update query to run when opening the base,
using Excel-type format (e.g. Sum([Loc A],[Loc B]) but with no success. I
feel there must be a way, but cannot fathom it out, and this is an essential
element of my base. Any ideas gratefully accepted. Thanks.
David

 

[Jeff Boyce]

David

I wasn't able to discern your current table structure from your description,
.... but I do know, from experience, that the transition from Excel to Access
is not always smooth. If your table(s) in Access are patterned after your
Excel spreadsheets, you (and Access) will struggle.

If you'll post a description of your table(s) structure, folks here can
better offer their observations.

--

Regards

Jeff Boyce
Microsoft Access MVP

Disclaimer: This author may have received products and services mentioned in
this post. Mention and/or description of a product or service herein does
not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John W. Vinson]

I have created a simple asset base in Access 2007 to record equipment, and
the number of each item held in various places (e.g. Item = PC, Loc A=10, Loc
B=6, Loc C=5). I want a field in my table to show the total held (e.g. 21). I
can do this in a report, but I want it in the table so it is visible in all
the forms. I've tried to create an update query to run when opening the base,
using Excel-type format (e.g. Sum([Loc A],[Loc B]) but with no success. I
feel there must be a way, but cannot fathom it out, and this is an essential
element of my base. Any ideas gratefully accepted. Thanks.
David

There are a couple of problems here. For one, it appears that you have a
different FIELD for each location. This is decent spreadsheet design, but is
simply wrong for relational databases! If each Item can be represented in one
or more Locations, and each Location can have one or more items, you should
use *three tables*:

Items
ItemID
<description of the item>

Locations
LocationID
<name of the location>

ItemsAt
ItemID <link to Items>
LocationID <link to Items>
Quantity

If there are 10 item X's at location A, you would have a record for Item X,
Location A, quantity 10. This will let you create a Totals query summing the
Quantity for each item, or for each location, as you need.

Secondly... you're intending to store the sum in the table. This is incorrect
design! Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost any calculation
will be MUCH faster than a disk fetch); and most importantly, it risks data
corruption. If one of the underlying fields is subsequently edited, you will
have data in your table WHICH IS WRONG, and no automatic way to detect
that fact.

 

[Guest]

I have created a simple asset base in Access 2007 to record equipment, and
the number of each item held in various places (e.g. Item = PC, Loc A=10,
Loc
B=6, Loc C=5). I want a field in my table to show the total held (e.g.
21). I
can do this in a report, but I want it in the table so it is visible in
all
the forms. I've tried to create an update query to run when opening the
base,
using Excel-type format (e.g. Sum([Loc A],[Loc B]) but with no success. I
feel there must be a way, but cannot fathom it out, and this is an
essential
element of my base. Any ideas gratefully accepted. Thanks.
David