Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Unanswered: Getting a Total of all Values (Not Just Top 25)

    I've set up a report that pulls the top 25 values of a field([ Total]) from a query ([Supplier]). It's easy enough to get the sum of the 25 values. Is there a way to put the sum of all the values(not just the top 25) on the same report?

    Thanks in advance,

  2. #2
    Join Date
    Apr 2004
    Kingsland, Georgia
    SUM - Returns the SUM of said field
    SQL SyntaxELECT SUM(fieldname) FROM tablename

  3. #3
    Join Date
    Mar 2003
    The Bottom of The Barrel
    Provided Answers: 1
    ummm, he's only pulling the top 25, that's the whole point of the post.

    You have a couple options here. If the query is simple and based on a single table, you could use the DSum() function in an unbound textbox. An example of that would be:

    =DSum("yourField", "yourTable", "somecriteria = something")

    If the query is based on a more complex query, you could use a subreport and place the "select sum()" statement there. Alternately, you could use a subquery, and attach the number to each record, that method is a little bit of a memory hog, but it will work just dandy if you aren't too concerned with optimal performance. That might look like this:

    SELECT TOP 25 yourField, yourOtherField, (SELECT Sum(YourField) FROM yourTable WHERE somestuff) AS yourTotal
    FROM yourTable
    WHERE some stuff
    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