Q: I have a database that contains monthly reports from various users. Each report needs to refer back to the previous report from that user for part of the content. The problem is that sometimes people skip reports (which is okay), so it isn't as simple as having a report relate to the previous month, or even the month before that. What's the simplest way to have one report relate to the previous report, which may be dated one or even six months prior to the current report?
A: The simplest way to do this, assuming you have a User ID field and a Report Date field, is to construct a relationship from the Reports table to itself that matches on these fields as follows:
Report (Report table) Report Previous (Report table)
User ID = User ID
Report Date > Report Date
This will let the current report relate to all previous reports. What you want, though, is to relate to the most recent previous report. If you change the Sort records option for the Report Previous side of the relationship so that it sorts by Report Date in descending order, then the "firs"" related record -- the one that would appear at the top of a portal -- would be the most recent one. So, even though the relationship relates to multiple previous reports, this calculation (or a related field on a layout) would give you what you want:
Previous Report Targets (calculation, text result) = Report Previous::Targets