none
How to group by a specific field in Access Office 365 App query RRS feed

  • Question

  • I have a table called Compensation Log that I am trying to make a query for. Compensation Log has an entry for each change in salary an employee has received and has a one-to-many relationship with my employees table via a lookup field. I want to create a query to show the Compensation Log grouped by the Employee field and with the Date field set to Max.
    Thursday, August 6, 2015 10:38 PM

Answers

  • Hi T,

    Sorry I forgot you were on Web Apps!

    In this case you may need to leave the salary Out of this query!

    Then use this Queries Results as an input table to link back to your  CompensationLog table and extract the salary for the know Employee and date.


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Tuesday, August 11, 2015 10:42 PM

All replies

  • Add both Tables to the Query Designer, link if required.

    Add the fields you require to the grid.

    Click the Totals Button on the ribbon

    Add Criteria under the Compensation Date. Select Max in the Total Row.

    Under Employee fields Select Group By in the total Row


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    • Proposed as answer by André Santo Friday, August 7, 2015 11:00 AM
    Friday, August 7, 2015 12:18 AM
  • Thank you for your reply!

    The problem is that all fields defaultly have "Group By" selected and it won't let me save the query if I delete the "Group By" values in the other columns. It says Your query does not include the specified expression "Salary" as a part of an aggregate function. I want it to show the salary that corresponds with the max date
    • Edited by T.EpicLand Friday, August 7, 2015 8:36 PM
    Friday, August 7, 2015 8:33 PM
  • Hi T,

    All Aggregate Queries require all fields to be a "Group By" or a calculation (aggregate) of some kind.

    Set the Salary as a Group by in the last right hand side field. As the MAX(Compensation Date) will eliminate all other dates then the only Salary to group by will be the last Salary.


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Sunday, August 9, 2015 8:58 PM
  • I only have three columns, Employee Name (which looks into the employee table), Salary and Date (both from the compensation log table). I Put the Employee Name column first, then Date, then Salary as you said and it still shows multiple entries for each employee. I've also tried Salary, Date, Employee

    Maybe group by is not actually want? The compensation log just has a an Employee ID, Salary, and Date field. I'd like to show the newest Salary for each employee. 

    Monday, August 10, 2015 10:21 PM
  • Hi T,

    Did you "Add Criteria under the Compensation Date. Select Max in the Total Row."

    This should only show one date per Employee.

    Are your Tables linked?


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Monday, August 10, 2015 11:23 PM
  • Both tables are linked based on the EmployeeID field. Below are the perimeters of each field in the query.

    Field: Full Name
    Table: People
    Total: Group By
    Sort: 
    Show: [x]
    Criteria:
    or:

    Field: CompDate
    Table: CompensationLog
    Total: Max
    Sort: 
    Show: [x]
    Criteria:
    or:

    Field: Salary
    Table: CompensationLog
    Total: Group By
    Sort: 
    Show: [x]
    Criteria:
    or:

    As for "Add Criteria under the Compensation Date." I am not sure what criteria i would add.

    Thank you for being patient with me, this really helps.

    Tuesday, August 11, 2015 5:37 PM
  • Hi T,

    Ahh, yes the Salary as a group by is populating all values of Salary!

    Change Salary from Group By to First.


    Brian, ProcessIT- Hawke`s Bay, New Zealand


    • Edited by ProcessIT Tuesday, August 11, 2015 9:52 PM
    Tuesday, August 11, 2015 9:52 PM
  • Do you mean changing the Salary's "Total" field from "Group By" to the text "First"? I am unable to do that because it's not available in the drop down. My options include the following:

    Group By
    Sum
    Avg
    Min
    Max
    Count
    StDev
    Var
    Expression
    Where

    Tuesday, August 11, 2015 10:22 PM
  • Hi T,

    Sorry I forgot you were on Web Apps!

    In this case you may need to leave the salary Out of this query!

    Then use this Queries Results as an input table to link back to your  CompensationLog table and extract the salary for the know Employee and date.


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Tuesday, August 11, 2015 10:42 PM
  • Wow, that is astonishing that microsoft would build something lacking this much functionality. Thank you so much for your help.
    Tuesday, August 11, 2015 10:58 PM
  • Hi T,

    Did you get it to work?

    Yes Web apps really should be called a beta release!

    Good luck with your development.


    Brian, ProcessIT- Hawke`s Bay, New Zealand

    Tuesday, August 11, 2015 11:13 PM