Combining latest fields from multiple related records
I'm just starting out with Filemaker, so excuse my lack of knowledge.
I've got a database that is storing information regarding subjects and various measurements of each of the subjects. It is structured so that there is a table called subject with subject names and a unique subject_id. This is related to a table or measurements using subject_id, and in the measurements are a list of 30 possible measurements like "height", "weight", "body fat", etc as well as the date of measurement. Each subject may multiple related measurement records, and each record may only be partially filled-out.
I'm trying to find a way to generate another "summary" table that automatically lists each of the subjects in the subject table and shows the latest measurements as of a particular date for each of the measurement fields. I want to be able to use this to "backtrack" in time and see what the latest measurements were on a given date. I've looked into making various calculation fields but couldn't find a way to do exactly what I want.
I want to be able to generate a table with the latest measurements as of some date (5/5/2005 in this example):
What's the best way to go about doing this? For some reason I have a feeling I might need to use a SQL statement, but I really have no idea where to begin.