FIRST function finds the earliest occurrence of a value in a subform’s field when sorted by another field in the same subform.
FIRST([Subform field].ValueField], [Subform field].SortField)
- Subform field: The subform containing the fields
- ValueField: the field whose value to find
- SortColumn: the field containing the values on which the sorting will be based
Suppose you are tracking the number of sick family members across households over time. You have a parent form called "Households" and a subform "Health Check-ins":
The subform contains a list of all the health check-ins, along with the date of the check-in.
|Family||Number of sick family members||Date of reporting|
You can add a calculated field to the parent household form shows the earliest reported number of sick family members for each family as a baseline using the following formula:
FIRST([Health check-ins].[Number of sick family members], [Health check-ins].[Date of reporting])