Query to select record with highest value

[friedgoldmole]

Assuming I have the following data in a table

Month TechID PlanUnit GrossHours
01/01/2017 10165 CARLI 37.5
01/01/2017 10170 THAMS 112.5
01/01/2017 10487 WATFD 167.5
01/01/2017 10541 IPSWI 160
01/01/2017 10541 TSIDE 80

In instances where any individual TechID has more than one entry for a given month (10541 in above example) I want to return only the one record where the GrossHours number is the biggest, in the above example that would be the one with PlanUnit: IPSWI and Gross Hours: 160.

I have been playing with Sub Queries but not come up with anything that works properly, any advice would be greatly appreciated.

Liam

 

[plog]

A subquery is the correct way to achieve this. It would be an aggregate query (meaning you will click the Summation/Sigma in the ribbon). In that subquery you would only have 2 fields--TechId and GrossHours. Underneath 'TechID' it should say Group BY and underneath GrossHours it should say 'Max'.

Save that query as '_sub1'. Then build a new query using the initial table and _sub1. You would join them via TechID fields and then the GrossHours fields. You would bring down all the fields from the table, and none from _sub1. Run it and you have your results.

 

[friedgoldmole]

This worked perfectly when I also included the Month in the sub query and main query, without that it didn't return all the expected records, probably due to techs having multiple months with the same gross hours. Thanks for your help!