Find values associated with most recent entry

Thanks in advance for any insight from the AT community!

I have a base that includes a bunch of school enrollment information. Some is static/historical, and some is updated monthly.

There is one table that with the list of schools as the primary key, and those schools are linked in another table for monthly enrollment tracking. From the latter is a form that schools complete to enter their monthly enrollment information. The form submission has a “Date Created” field.

I want to be able to provide the updated enrollment total based on the MOST RECENT entry date. Because different schools provide there updates on any given date of the month (and some haven’t submitted since the previous month), I can’t just filter by date because I will lose some data. For instance, if I put a filter on the date for the last month, it won’t catch a school that last submitted five weeks ago.

For example:

Monthly Registration Tracking (School names are linked to the Schools Table)
School A - 6/8/2021 - 400 students
School B - 7/10/2021 - 115 students
School C - 8/1/2021 - 225 students
School B - 8/15/2021 - 123 students
School C - 9/1/2021 - 220 students
School B - 9/8/2021 - 125 students

I would want a roll-up or some field in teh Schools Table to show the following:
School A - 400 students
School B - 125 students
School C - 220 students

Thanks!
Eliza

I had a very similar question that @Kamille_Parks answered for me this past week @Eliza_Bryant

The podcast episode shows how you can use MAX(values), along with rollups and lookups to pull data from a record with the most recent date. I was able to successfully implement the solution after watching the episode briefly, it’s pretty much plug and play in the example @Kamille_Parks showcases.

Hope that helps!

3 Likes

This was great! Thanks!! It’s a little round-about, but it’s not overly complicated. Thanks for sharing!

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.