How to Exclude the Most Recent Record from a Calculation
I am working with a database that, in part, tracks a medical screening and its results. As part of the tracking, each person screened is categorized according to the frequency of screening. The calculated field that I have used to categorize use includes evaluation of all completed visits. Visits include appointment made and results posted in the database along with self-reported screening dates when screening was completed elsewhere. The end user now wants to exclude the most recent screening appointment from the categorization process.
There are two tables involved in this calculation: Clients and Screening Appointments. My calculation determines the most recent screening date, which is then used to determine the category. In response to the exclusion of the most recent screening appointment, I created a new Screening Appointments TO, set a descending sort order for the appointment date and created two relationships between the two Screening Appointments TOs: Client_fk = Client_fk and ApptDate ≠ ApptDate. I then substituted the ApptDate from the new TO in my Most Recent Screening Date calculation and succeeded in excluding all non-self-reported appointment dates from evaluation.
What do I need to do to exclude only the most recent screening appointment date? I want my calculation to evaluate frequency of use based on the second to last completed screening appointment.