none
Aggregating amounts RRS feed

  • Question

  • I have this subform:

    SELECT [Management Accounting table].Date_To_Budgets,

    [Management Accounting table].Date_Returned_To_SCM,

    [Management Accounting table].Date_To_Assets,

    [Management Accounting table].Date_Returned_From_Assets,

    [Management Accounting table].Select_Fund,

    [Management Accounting table].Project,

    [Management Accounting table].[Select Sub-Programme],

    [Management Accounting table].Cost_Centre,

    [Management Accounting table].[Item],

    [Management Accounting table].Supplier,

    [Management Accounting table].Amount, SLD_Number

    FROM [Management Accounting table];

    ...and I need to get its total amount. I don't know how to aggregate it.

    It's subform to this one:

    SELECT SLD.SLD_Number, 
    SLD.Date_Received, 
    SLD.Time_Received
    FROM SLD;


    Tuesday, April 25, 2017 1:11 PM

Answers

  • As far as my knowledge went, aggregating/calculating values should be accompanied by a Group By clause. In this case it did not work (I even included fields from the main form in the GROUP BY clause).

    The solution I found on stackoverflow.com was this:

    SELECT SUM([Management Accounting table].Amount) AS [Total Amount]

    FROM [Management Accounting table];

    I made this a sub-form.

    • Marked as answer by MarcusKgosi Friday, April 28, 2017 7:34 AM
    Friday, April 28, 2017 7:34 AM

All replies

  • Could you share us your source data and expected data? You shared us two queries, what do you want to sum? Do you want to sum amount by SLD_Number? If so, I suggest you try something like below:

    Select SLD_Number, Sum(Amount) from [Management Accounting table]
    
    Group by SLD_Number

    A screen shot about your issue would be helpful.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, April 27, 2017 7:37 AM
  • I already solved it.
    Friday, April 28, 2017 5:36 AM
  • I am glad your issue has been resolved. 

    I would suggest you post a new reply to share us the solution, and mark it as answer to close this issue. Then others who run into the same issue would find the solution.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 28, 2017 5:46 AM
  • As far as my knowledge went, aggregating/calculating values should be accompanied by a Group By clause. In this case it did not work (I even included fields from the main form in the GROUP BY clause).

    The solution I found on stackoverflow.com was this:

    SELECT SUM([Management Accounting table].Amount) AS [Total Amount]

    FROM [Management Accounting table];

    I made this a sub-form.

    • Marked as answer by MarcusKgosi Friday, April 28, 2017 7:34 AM
    Friday, April 28, 2017 7:34 AM
  • I struggle to make screenshots on the PC I'm using.
    Friday, April 28, 2017 7:34 AM