Adding fields with a calculated field

[Miti]

I have a table which updates with information from
another database program. Six fields in the table store
numeric values eg : Item1 220.00 Item2 22.00 and so on.
There is then a total field that stores the total of
these six fields in the record. The other program does
this with a simple calculated field eg: =[Item1]+[Item2]+
[Item3] etc. If any of the fields are changed the total
field automatically updates I cannot find a way to do
this in Access. I have to keep both tables identical to
maintain synchronization - can anyone help me on how I
can make a total field in Access that will work this
way. Your help would be greatly appreciated.

 

[Cheryl Fischer]

What you are trying to do is "commit spreadsheet" on a database. The
structure you describe is fine in a spreadsheet but not for a database,
which should contain normalized data to function correctly and efficiently.

Given that you may not have the authority to change the structure of these
databases, I would suggest the following:

1. Update data ONLY in a form or forms, never directly in the table, as
Access does not have triggers which can force an update of that Total field.

2. Create and save an Update Query which will update the Total field with
the sum of your Item fields.

3. In any and all of the forms which allow updating of an Item field, add
code to the AfterUpdate event of each Item control which will run the above
query.


To read up on and get a better feel for Normalization, here are a couple of
links:

Normalizing Your Database: First Normal Form
http://databases.about.com/library/weekly/aa081901a.htm

"Database Design for Mere Mortals" Amazon link
http://tinyurl.com/2c6bp

 

[Rebecca Riordan]

You can't, at least not at the table level. What you can do is run an
update query that does the calculation.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...