Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old February 14th, 2005, 11:14 PM
Registered User
 
Join Date: Feb 2005
Location: , , .
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Default sum

I forgot how to add.....
I have a table "Estimate" that has field of "Cost", "Amountpaid1", "Amountpaid2" and "AmountDue".
How can have my table field "AmountDue" updated automaticly? (Sum-AmountDue1-Amountdue2)?
Or should it be on the form?
I forgot how...




Thanks
IB
  #2 (permalink)  
Old February 15th, 2005, 12:06 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: , , USA.
Posts: 1,093
Thanks: 1
Thanked 12 Times in 11 Posts
Default

Hi IB,

You can't (sensibly). Never include calculated values in tables (with the exception archives of some sort). They belong in queries. The following query will give you what you want:

SELECT Cost, AmountPaid1, AmountPaid2, [Cost]-([AmountPaid1]+[AmountPaid2]) AS AmountDue
FROM Table1;

As an aside, a better table schema would place your two AmountPaid fields in a seperate table on the many-side of a 1:M relationship with your table that contains cost information. Including the AmountPaid fields on your primary cost table creates what are known in normalization nomenclature as "repeating groups". They breed nasty insertion, deletion, and updating anomolies as your db design increases in complexity. (What happens if someone has to make 3 payments?). Tables that contain repeating groups are not even in 1NF (1st Normal Form), and a well designed relational db should achieve at least 3NF. I know you're probably thinking, "What the #(*$&! is he talking about." ;) But it all starts with a solid table schema.

HTH,

Bob

  #3 (permalink)  
Old February 15th, 2005, 08:53 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I concur with the Bobenator.

-Never store calculated fields in tables.

-Always provide for n number of instances (in this case payments. What if a customer was put on a payment plan for a year?)

-The query calc is good for this structure, but if you do normalize your tables you will need a different query, of course.



mmcdonal


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to sum in for-each Gjella XSLT 1 October 2nd, 2006 08:48 AM
Help: Running Sum (or Cumulative Sum) timdasa VB Databases Basics 1 August 22nd, 2006 03:12 PM
sum utarian Access 2 March 28th, 2005 09:42 PM
need sum help! jbik BOOK: Beginning PHP, Apache, MySQL Web Development ISBN: 978-0-7645-5744-6 0 December 20th, 2004 05:27 PM
SUM or What bertlf Pro VB Databases 2 November 29th, 2003 02:44 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.