Results 1 to 10 of 10

Thread: sum function

  1. #1
    Join Date
    Dec 2005
    Posts
    3

    Unanswered: sum function

    I have two tables.

    Table 1. 1 Column, ~10k rows.
    Table 2. 1 Column, 1 Row.

    I would like to get the sum from Table1.Column1, and input that into the only cell in Table2.

    I thought this would be extremely easy, but I just can't figure it out. If you do answer this question, could you do so with the proper syntax b/c I can't get mine to work.

    Thanks!

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    1. Why are you doing this? Is there a really, REALLY good reason to store data you can get at any time from table1 into table2?

    2. You indicated you have made an attempt. What syntax have you tried?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    This worked for me:
    Code:
    INSERT INTO Table2 ( OneCell )
    SELECT Sum(Table1.Col1) AS SumOfCol1
    FROM Table1;
    but only as an Append query. You won't get an Update query to work without a lot of pain.

    Having said that, if you do need the Update query, what you need to do is a Make Table query based on the sum of Column1 of Table1. Call the new table say SumCol1, and the field name Col1.

    Then you can make the update query as follows:
    Code:
    UPDATE Table2, SumCol1 SET Table2.OneCell = [SumOfCol1];
    If you need to know the technical reasons for the above, read up on Updateable Queries in the help file.

    Sam

  4. #4
    Join Date
    Dec 2005
    Posts
    3
    Sam,

    Thank you. I also figured out that the append query works, but ultimately I would like table2 to contain about 15 columns (still only 1 row), each storing it's own value for future reference. Which is why I would like to use the Update query to continue updating each of the 15 columns as roll through my data-manipulation steps.

    I read up on Updatable Queries, but to be honest it didn't make much sense. Can you just give me a quick explanation as to why MS Access thinks I need an updatable query for this?

  5. #5
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    The 1-2 punch sequence I gave you will work, as it produces an updateable query. Try it, you'll like it.

    To explain briefly. In general, Access only allows data to be modified in tables, nowhere else. In non-totals queries that don't contain duplicate records, also, Access will allow it, as there is no - or low - possibility to corrupt data. However, in a query that contains duplicate records, or a totals query (a query that uses any aggregate function, such as Sum() in your case, is a totals query) that acts on multiple records (Sum(Col1), for example) creates a non-updateable query, as there is a very high possibility of corrupting data. Access will not allow this to run in a DELETE or UPDATE query.

    Ergo, when your original UPDATE query called for SET Col1 = (SELECT Sum(Col1...., etc, you were doing exactly this. However, when you first create the interim table using a Make Table query, that was allowed, because Access allows a totals query in a MAKE TABLE or APPEND query, as the query is not modifying existing data, it is simply adding new records and new data which can be hunted down and deleted if necessary.

    Once you have the interim table, you no longer need an aggregate function to do your UPDATE query, as I showed you the exact code for that query. Just be sure to delete the table in between runnings of the program.

    Of course, your Make Table query can work for multiple columns as well. Your UPDATE query will simply SET each column of Table1 based on the contents of a different column in the interim table.

    Makes sense now?

    Sam
    Last edited by Sam Landy; 12-21-05 at 18:50.

  6. #6
    Join Date
    Dec 2005
    Posts
    3
    Sam,

    Thank you again, that certainly helps.

    Rather than having to use the MAKE TABLE query each time I want to 'copy' a calculated data value from one of my tables into a repository table, would it be easier/possible to simply APPEND the calculated value to a temporary table and then use the next query to take the last value/row in the temporary table and insert it into my repository table. This would in essence be using the temporary table as a half-way-point to the final repository table.

    ?

  7. #7
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    You don't want to do that, because Access doesn't always store the last appended record in a table as the last record. Access uses a complicated algorithm to calculate where it wants to store a record.

    Besides, you can automate the deleting of the previously-made table, re-running the Make Table query, and the UPDATE query, all from within a macro or VBA.

    Good luck,

    Sam

  8. #8
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004

    Talking

    Quote Originally Posted by JonOakdale
    Sam,

    Thank you again, that certainly helps.

    Rather than having to use the MAKE TABLE query each time I want to 'copy' a calculated data value from one of my tables into a repository table, would it be easier/possible to simply APPEND the calculated value to a temporary table and then use the next query to take the last value/row in the temporary table and insert it into my repository table. This would in essence be using the temporary table as a half-way-point to the final repository table.

    ?
    Being as you're not actually keeping (storing) your data permanently, why not just have a Query / Form calculate it on the fly, spit it out in a Report and be done with it? Creating a Temp Table seems to be just a step not really needed as it seems once you calculate it and send it to the table, you are essentially done, until the next time for which it seems you are Deleting that last update and replacing it anyway. Just trying to reason the logic in it is all.

    have a nice one,
    BUD

  9. #9
    Join Date
    Sep 2002
    Location
    South Wales
    Posts
    580

    Unhappy Storing Calculated Values

    Please explain why you are storing a value which can always be calculated in a table.

    I suspect you are heading for a heap of trouble unless you have an exceptionally good reason for doing this.
    Windows Server 2003-8 / Terminal Services / SQL 2000 / Access 2003 / Office 2003-7 / Exchange 2003-7 / Blackberry Enterprise Server / AutoCAD / Lambert And Butler / Red Bull

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by garethdart
    Please explain why you are storing a value which can always be calculated in a table.

    I suspect you are heading for a heap of trouble unless you have an exceptionally good reason for doing this.
    He ignored that question the first time I asked it, I wouldn't bother...
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

Posting Permissions

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