Returning most current data from related tables with multiple matching fields
Using FM Pro 11 on a mac. I'll try and keep my problem as concise as possible...
I have a membership table showing all membership renewals for our Association over the past 2 years.
I have a separate table showing a list of all our accredited instructors, who have to be members to retain accreditation.
I have linked the tables via a calculation field called fullname = first name + second name fields
I am trying to reference the membership table and return to the instructor table, current membership number and expiry date for all those in the instructor table - that way we can see who has expired, and who is due to expire and proactively send reminders etc etc.
However, in the instructor table I am getting some out of date data from the membership table. This only occurs when an instructor has been a member for more than one year i.e. the membership table has multiple entries for the instructor's name. The relationship link then returns the first record matching the name from the membership table, however this is an out of date piece of info, since the table was created in chronological order of membership renewals, and hence the most current info is not returned to the instructor table.
I need to query the member table and return the latest matching record from the multiple entries where there is an instructor name match from the instructor table.
Not sure what the most effective way of achieving this is, any help would be much appreciated. Maybe using the fullname calculated field is not the best way.