Results 1 to 7 of 7
  1. #1
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122

    Unanswered: add a total field

    I need to wrtie a query that adds the rows in a table and places the total in a totals field. example:

    cash1 cash2 cash3 cash4 cash5... total
    10 12 10 5 5 42


    i have already created the TOTAL field in my table but now i need to fill it. Can i write a query that sums a row?

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Saving derived data is not a good thing... That is what reports are used for - displaying those totals. What happens if you need to update that row? Do you overwrite the original total? Do you need to maintain the original total (possibly for historical reasons)?

    As to your question: Yes you can ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Dec 2002
    Location
    Phoenix, AZ
    Posts
    122
    my table is rebuilt everyday and i was hoping to add a total field and rebuild that everyday too.

  4. #4
    Join Date
    Dec 2006
    Posts
    53
    Yeah, as Mr. Owen said, the best way to accomplish this is to create a report based on the table. Then you can even add some formatting to make it easier to look at every day!

  5. #5
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    CC

    You may note that Jet in this forum is asking a similar question about adding totals.

    Again I agree with Mike - Unless its historical and you need to track a specific total stay away from this. It can add-up to some real problems later.
    Beside if you did want to source a totals value at a later date - using dates for all enteries would facilitate this.

    Use your queries and reports to do all the work.

    Gareth

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agreed... don't store derived data..

    the only reasons to break that rule is if there is a significant performance penalty or other user requirement. A such user requiremnt could be the userrs are unable to handle the SQL to do the calculatuions, or oif there is a financial reason ie the figures must must agree with soem other piece of data.

    if the data is passive view (ie it isn't going to change or be updated by any onliunme process) then it may make sense to do soem form of rollup totaling or whatever.. especailly if the users are in a hurry to get 'their' information (when ever are they not). Assuming that "my table is rebuilt everyday and i was hoping to add a total field and rebuild that everyday too." holds AND there is no online update AND its a passive view of the data then storing the totoals may make sense in this case. But this is a special case, and rules are meant to be broken. Generally storing derived values is not sensible.

    so if you must persist in storing the totals consider running an update query after importing the data

    open the query designer, select the relevant table, tell the query dsigner you are using an update query

    or even view the SQL in the query designer and type soemthign like

    update <mytable> set <mycolumn>=<mycolumn1> + <mycolumn2> + <mycolumn3> + <mycolumn'n'>

    eg update DTBankings set Total = Cash1+cash2+cas3+cash4+cash5
    I'd rather be riding on the Tiger 800 or the Norton

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

    Storing data

    I disagree on storing summed data. As long as the user is going to be entering the data via the interface, storing summed data offers you the benefits of not having to recalculate all the fields when designing your reports, especially when you have millions of totals to calculate. Sorry healdem and Mike but when it comes to totalling lots of records, calculating the sum each time only makes your reports run slower. I wish we had a meeting where we could discuss the pro's/con's but in my experience, storing the summed value has always been beneficial in almost everything I've designed.

    In regards to the original post, again, if your data entry is going to be strictly through the form, then you just need to put in AfterUpdate events on all the fields (i.e. Cash1, Cash2, Cash3, Cash4, etc...) In the AfterUpdate event of each Cash field, you would have code like this below:

    Me!TotalValueField = IIf(IsNull([Cash1]), 0, [Cash1]) + IIf(IsNull([Cash2]), 0, [Cash2]) + ......etc

    Assuming also that you've formatted each of your Cash fields to Currency and where TotalValueField is the field in your database which holds the totals.

    You do need to be concerned if someone goes back in and changes a Cash field (whether or not your storing the summed value!!) so it would be a good idea to possibly have a DateChanged, ChangedBy (here you would get the user's loginID (see attachment)), CashFieldChanged, and OldCashValue, NewCashValue fields in the table (personally, I'd make a new "logging" type of table which records "changes"). Here's where you get different ways of doing this and you'll get a lot of suggestions.

    This is of course, simplified and doesn't solve the more complex issues of changing data when it comes to totals but I've worked with a lot of data totals consisting of millions of records where I've only been off by .003% which is pretty good considering the amount of information I stored.

    If I had to calculate Cash1, Cash2, Cash3, etc... on a report or in a query, I would have had a pretty rough time doing it without a stored summed value on lots of records. There are a lot of people who support the theory that you never store the summed value in the table. I'm not one of those people. I think there are a lot of benefits of doing it and when you deal with an entire companies worth of data (i.e. in the millions of records), calculating values on the fly is not a quick way to derive totals.
    Last edited by pkstormy; 12-28-06 at 13:39.
    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
  •