Results 1 to 5 of 5
  1. #1
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accumulation of Amounts (XP)

    I have an issue with a query where I would like to sum the amounts of various periods (1 to 12). Stated otherwise, the query result would return the bunit, account and a single amount (ie periods 1 to 12).

    My field headings are: BusinessUnit, Period,Account,Amount

    What can I say, I am more at home with XL than Access. But I am learning.

    Thanks,
    John

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Accumulation of Amounts (XP)

    Create a query that returns the four fields, then click the Totals button on the toolbar (it is the same button as the XL autosum button.)

    This will add a Total line to the query grid, with groupby written in foir each field. For the amount field, change groupby to sum.

    This will give four fields in the output, bunit, account , a period and a total for that period.

    Is this what you want?

    If you want to sort the results, you can use the sortby row of the grid.
    Regards
    John



  3. #3
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accumulation of Amounts (XP)

    John,

    I understand the concept but the results are not what I expected. I expected to see one line returned with the sum of the values of each period. In this case the sum is zero.
    Attached Images Attached Images

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Accumulation of Amounts (XP)

    If you just want to see

    2005 0

    Then remove both Period and Account from the query, so that you Sum Amount when grouped by period.
    Regards
    John



  5. #5
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accumulation of Amounts (XP)

    John,

    I removed that period and that did the trick.

    Thanks,
    John

Posting Permissions

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