Results 1 to 11 of 11
  1. #1
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93

    Question Unanswered: Adding fields in a Form

    I have a table that has 8 fields, I would like the data I enter in those eight fields to be added together in another field

    example:
    property1.....to ....property8 are the fields the user enters the data in, and propertytotal is the field where these fields are added together.

    I would like to find the easiest way to do this, if it can be done without setting up a query.

    Thanks
    Will Dove
    working hard is better then hardly working ...

  2. #2
    Join Date
    Feb 2004
    Posts
    90
    In the form footer, put an unbound textbox and in it's Control Source property put:

    =[Property1] + [Property2].....etc
    Last edited by Goldy; 08-16-06 at 04:21.

  3. #3
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93
    That works for adding them together, however I need the data to add together, and then be placed in a table, in a field called PopertyTotal

    I tried to change the Control source of the PropertyTotal and it did the adding, but it did not input the data in the table.
    Will Dove
    working hard is better then hardly working ...

  4. #4
    Join Date
    Nov 2003
    Posts
    1,487
    Well...I supose one way you could do this would be to place this into your Form's BeforeUpdate event via the VBE:

    This assumes you have a textbox on your Form which is bound to the propertytotal field in table
    Code:
       Dim PropTotal As Currency
       TransTotal = Nz(Me.Property1, 0) + Nz(Me.Property2, 0) + _
                    Nz(Me.Property3, 0) + Nz(Me.Property4, 0) + _
                    Nz(Me.Property5, 0) + Nz(Me.Property6, 0) + _
                    Nz(Me.Property7, 0) + Nz(Me.Property8, 0)
        Me.PropertyTotal = PropTotal
    If there is no PropertyTotal textbox field on your Form and you simply want to update the record with the row total then use this code instead (in the BeforeUpdate event):

    Code:
       Dim PropTotal As Currency
       Dim SQLstrg As String
       TransTotal = Nz(Me.Property1, 0) + Nz(Me.Property2, 0) + _
                    Nz(Me.Property3, 0) + Nz(Me.Property4, 0) + _
                    Nz(Me.Property5, 0) + Nz(Me.Property6, 0) + _
                    Nz(Me.Property7, 0) + Nz(Me.Property8, 0)
       SQLstrg = "UPDATE Propertys SET PropertyTotal=" & PropTotal & _
                 " WHERE PropertyID=" & Me.PropertyID & ";"
       CurrentDb.Execute SQLstrg

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  5. #5
    Join Date
    Nov 2004
    Location
    Norway
    Posts
    441
    1 - the property1-N field seems like an unnormalized structure (1NF) that is probably going to cause you problems
    2 - storing a calcultated value also goes against relational prinsiples, and is likely to going to cause you problems

    Why not try to normalize, then you can use standard SQL to do whatever sums you like - on the fly. Here's some reading http://www.r937.com/relational.html
    Roy-Vidar

  6. #6
    Join Date
    Dec 2005
    Location
    Somewhere on the 3rd Rock
    Posts
    93
    Thanks for the replies, I will be changing this to another method that makes more sense.

    Will Dove
    working hard is better then hardly working ...

  7. #7
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    If it's in the same table, then in the After Update event of your totals text box put Me.PropertyTotal = Me.TotalsTextBox. Having said that, you should not really put the value of a calculated field in your table.

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Putting values of a calculated field in a table

    I keep reading about not putting calculated values in a table. I don't know where this stems from (maybe they're teaching this these days or it's an old teaching phylosophy) but I can tell you from someone who's developed applications for over 15 years (including many accounting/loan applications/purchase order/travel voucher type packages) that there are SEVERAL reasons you DO want to store calculated values in a table.

    If anyone's worked with or has programmed these type of programs, or other consistently calculated programs, you'll find it is a complete pain in the #@$ not to store calculated values. Especially when you're asked to sum the totals of a certain accounting # or a certain group/category of Travel Vouchers and you get into millions of records.

    I'd rather total up a single field with a stored calculated value verses having to recalculate the values of adding Field1 + Field2 + Field3 + Field4, etc., etc., on every query/report and making the query even more complicated (especially when you have to get complex). This also only makes your queries/reports run that much slower and to me, doesn't seem very efficient (I'm not talking about storing the sum of several records (which is another discussion) but instead the sum of several fields of 1 record.)

    Yes - it is true that you have to be careful on the programming end when you do this and also when values are changed (but this is all done via the interface - how many users are editing table values??) The phylosophy of never storing a calculated field value to me is simply not correct and seems like a dated phylosophy.

    I don't know where this rumor stems from but again, from my point of view, it's WRONG! Maybe this was true when disk space was a limitation and you tried saving space by not creating additional fields. I can see this phylosophy applied for certain situations but to generally say - "Don't ever store calculated fields" is something (from experience) that I very much disagree with (no matter who wrote it in whatever book).

    Sorry but I had to put in my 2 cents on this issue (it rubs me wrong and it's been a bad day - but it's kind of like the issue of people always using BOUND forms and then saying MSAccess isn't an Enterprise level software because it can't handle multiple users (which by the way - I've proven it can be (USE UNBOUND FORMS!!! and object-oriented techniques) - I designed the program to track the entire Midwest Energy Conservation program (10 gigs of info) using MSAccess and SQL Server)). Yeah, yeah - I know, I said SQL Server. But as I've stated so many times on this board, it's all in the way you set up the tables and how you write the code which determines the level of your program. I'm hoping that all you MSAccess veteran geeks out there who have used the true potential of MSAccess agree with me. MSAccess is a GREAT front-end tool if used correctly. It can also be a very good db server, again, if used correctly (but using jet tables is where I would agree that MSAccess is not a good Enterprise solution). But MSAccess and SQL Server combined - an AWESOME combination with the Enterprise power there if you know how to use it!!

    I really, really hate the bad rap MSAccess gets on this issue from inexperienced programmers!! - There - I've vented.

    (I'll probably have a few responses on this post.)
    Last edited by pkstormy; 08-25-06 at 18:42.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by pkstormy
    I keep reading about not putting calculated values in a table. I don't know where this stems from (maybe they're teaching this these days or it's an old teaching phylosophy) but I can tell you from someone who's developed applications for over 15 years (including many accounting/loan applications/purchase order/travel voucher type packages) that there are SEVERAL reasons you DO want to store calculated values in a table.
    It stems from Boyce Codd’s rules of normalisation which he wrote about in 1970. And for the last 36 years mainstream relational database theory has been underpinned by these normalisation rules. In particular, if you visit the link mentioned by RoyVider and read the bit about the Third Normal Form, it explains how storing calculated data breaks the rule.

    The philosophy behind relational databases is one of integrity. Suppose you have a calculated field based on other fields in a table. Then you have to programmatically via the front end, have to put steps in place to ensure the based data and calculated data remain in sync. This means every time someone develops a new interface they have to be aware of this constraint and implement it. Imagine if you have 30 or 40 designers all doing this. There’s also the risk that some smart alec decides to go behind the scenes and edit the data directly in the table without updating the associated calculated fields. Databases following the normalisation rules ensure that the integrity is handled within the schema and therefore by the database engine. Once you start to break the rules you have to rely on the abilities of the programmers to ensure that the front end maintains the integrity of your data. This is rather re-inventing the wheel as well as carrying extra risk.

    Quote Originally Posted by pkstormy
    If anyone's worked with or has programmed these type of programs, or other consistently calculated programs, you'll find it is a complete pain in the #@$ not to store calculated values.
    Agreed, there are times when it’s simply not practical. However, in my experience these times are by far the exception rather than the rule. The contributors are right to point out what is considered to be good practice in database design particularly if you are new to the subject. You need a damn good reason to do otherwise.

    Quote Originally Posted by pkstormy
    I'd rather total up a single field with a stored calculated value verses having to recalculate the values of adding Field1 + Field2 + Field3 + Field4, etc., etc., on every query/report and making the query even more complicated (especially when you have to get complex). This also only makes your queries/reports run that much slower and to me, doesn't seem very efficient (I'm not talking about storing the sum of several records (which is another discussion) but instead the sum of several fields of 1 record.)
    From my reading literature, the modern view is that operational databases running at transactional level are designed to conform to normalisation rules. However, for reporting and managerial information of large databases where process time is lengthy, the data is extracted to a data warehouse (star schema) style of architecture (or others such as OLAP), where the Codd’s rules aren’t followed but the organisation of the data is optimised for aggregation and repeated calculations.

    That's my 2 pence worth (or more colloquially "two penneth")
    Chris

  10. #10
    Join Date
    Nov 2003
    Posts
    1,487
    Well...there ya go.

    Thanx Chris...we got far more than our moneys worth.

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Stored Values

    A 1970's phylosophy? I agree with the normalization rules but in 1970, a 1 megabyte disk was considered the coupe de lux and there were a lot of theories which are dated (I'm wondering...did the theory of data warehouse even exist in 1970? If so, is it the same?). Maybe it's time to change some of these phylosophies and not just say "that's the way it's always done according to article XXXX!" (but article YYYY says to do it that way!). From my point of view, I like storing the summed value in certain programs such as the ones I mentioned - it sure made those specific programs a lot easier for me to work with and if I followed the rule of never storing the sum in a field, they'd still be working on the program (I guess it would keep some programmers in a job).

    I had a developer I hired once who was taught (just out of college) that the city, state, zip code, etc should each be in their own table. To return a single address you only had to link a 1/2 dozen tables together.

    And the database I'm currently working with at my company now uses some 1980's phylosophy where you repeat the same 4 fields in each table so 600 tables each have 4 fields which contain the exact same data. Which means to get data from 5 tables you have 5 x 4 = 20 linked fields and normalization is out the door (and a rather big company which I won't mention wrote this program!). There's a lot of "DIFFERENT" crazy phylosophies out there and I think some of these people who wrote these articles weren't in the "real" world business logic, wrote a few programs to do certain things and came up with some theories that "all" programmers and programs should do it this way.

    My point is that just because someone wrote "this is the way to do it", doesn't mean we should gospely always follow that rule. Do what's best for the company and the design of the program. I've yet to see 2 programmers design the same program the same way. As an added comment regarding the company I currently work for, I'm not sure why normalization wasn't used for this application, but it seems to be what was best for the application and the company. The one thing I have learned though, is the rules are never the same and each place has "their own way" of doing things. That doesn't necessarily mean it's the right way, but it is the way you must conform to if you plan on working there.
    Last edited by pkstormy; 08-28-06 at 17:36.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •