Simple Max Date Question

[knowshowrosegrows]

I cant tell you how much you all are teaching me and I am GRATEFUL.

From the below table I need a query that returns only the max date for each different MPI.

Test Table
MPI	policy_start_date
75918	10/1/1999
75918	12/9/2014
179938	12/22/2009
179938	5/1/2015
179938	10/2/2015
67980	4/1/2015
67980	5/1/2015
67980	10/15/2015
266811	4/1/2014
266811	5/1/2015
255531	12/9/2014
255531	6/1/2014
66049	10/15/2015
66049	10/1/1999
66049	5/1/2015

[JoeM]

Do an Aggregate (Totals) Query.
- Create a new query
- Add the two fields you show above
- Click on the Totals icon in the Query Builder (looks like a Sigma)
- Change the Totals line entry of "Group By" under the policy_start_date field to "Max"

View your results.
This "Groups" your data by the MPI field, and takes the Max date value of each distinct group.

[knowshowrosegrows]

Thanks for you reply.

Will this work if I have more fields in the query?

[JoeM]

Yes. Those would just have the phrase "Group By" under their total rows too.
Just be aware that it will have a listing for each distinct combination of all your Group By fields. So you want to be careful not to include any fields that you want ignored in your grouping.

[knowshowrosegrows]

Thanks so much!