1 Reply Latest reply on Jun 29, 2010 2:22 PM by philmodjunk

    Returning most current data from related tables with multiple matching fields



      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.

        • 1. Re: Returning most current data from related tables with multiple matching fields

          Hopefully that fullname calculation is First name & Second Name--not First Name + Second Name.

          It'd be much better design to link by a serial ID field. Names are not unique and subject to change--just to name two potential problems.


          Neither of those details affect the issue at hand, however.


          You have two methods you could use to access the most recent membership record:

          You can use the Last () function to refer to related data in a calculation and it would reference the last related record instead of the first.


          You can also go into Manage | Database | Relationships, find the relationship line linking your two tables (table occurrences actually) and double click it to bring up the details on the relationship. There's a sort option you can choose to sort the records in descending order--assuming you have either a date field or serial number field in membership that you can sort on to get this order.