none
Sum Query based on date values RRS feed

  • Question

  • Hi,

    I want to Sum values from qryWeeklySE_WorkPackEmployee_GroupBy where Activity Date in this query lies between Start and End Date from query qryWeekly_Verified_Report.

    This is too complex for me. I've tried full day trying lot of different stuff but nothing has worked for me yet!

    Attaching screenshot. Help please!

    Thanks,

    K

    Wednesday, June 22, 2016 8:26 AM

Answers

  • Join the two queries on SupervisorID vs SupervisorID, AreaID vs Area and WorkPackID vs WorkPack, by dragging the field from the query on the left and dropping it on the corresponding query on the right.

    Make sure that the Totals button in the Show/Hide group of the Design tab of the ribbon is highlighted.

    Add SupervisorID, AreaID and WorkPackID to the query grid, and leave their Total option set to the default Group By.

    Assuming that you want to sum WorkingHours, add WorkingHours to the query grid and set its Total option to Sum.
    If desired, do the same for other fields you want to sum.

    Add ActivityDate to the query grid, and set its Total option to Where. This will automatically clear the Show check box for this column. In the Criteria row, enter

    Between [Start_Date] And [End_Date]


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KhurramKZ Friday, June 24, 2016 5:45 AM
    Wednesday, June 22, 2016 2:24 PM

All replies

  • Do the queries need to be joined on one or more fields, such as SupervisorID?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 22, 2016 1:05 PM
  • Yes, on Supervisor, Area and WorkPack fields join will be applied.

    K

    Wednesday, June 22, 2016 1:48 PM
  • And should the query be grouped on all of those fields?

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    Wednesday, June 22, 2016 1:58 PM
  • Yes, even if it groups on SupervisorID only it'll do for now.
    Wednesday, June 22, 2016 2:15 PM
  • Join the two queries on SupervisorID vs SupervisorID, AreaID vs Area and WorkPackID vs WorkPack, by dragging the field from the query on the left and dropping it on the corresponding query on the right.

    Make sure that the Totals button in the Show/Hide group of the Design tab of the ribbon is highlighted.

    Add SupervisorID, AreaID and WorkPackID to the query grid, and leave their Total option set to the default Group By.

    Assuming that you want to sum WorkingHours, add WorkingHours to the query grid and set its Total option to Sum.
    If desired, do the same for other fields you want to sum.

    Add ActivityDate to the query grid, and set its Total option to Where. This will automatically clear the Show check box for this column. In the Criteria row, enter

    Between [Start_Date] And [End_Date]


    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by KhurramKZ Friday, June 24, 2016 5:45 AM
    Wednesday, June 22, 2016 2:24 PM
  • Hi KhurramKZ,

    you had mentioned that you want to sum the value but you did not mentioned which values you want to sum.

    did the suggestion given by the Hans Vogelaar MVP worked for you. did it solves your problem?

    if yes mark the suggestion given by him as an answer.

    if still problem is exist please let us know what you had tried and what result you get.

    so based on that we can try to provide further suggestions.

    Regards

    Deepak

    Thursday, June 23, 2016 1:43 AM
    Moderator
  • Sorry for late reply, I got stuck in another job. Thank you so much for your help again, it solved my problem )

    Regards,
    K

    • Marked as answer by KhurramKZ Friday, June 24, 2016 5:45 AM
    • Unmarked as answer by KhurramKZ Friday, June 24, 2016 5:45 AM
    Friday, June 24, 2016 5:45 AM