trying to load a field in one table (all records) with a field in the most recent record in another table

I suggest that you make a table called FX Rate Today.  Add 1 record to it even though there are no fields yet.  It will be record ID#1.

In your FX Rates Table make a field called link to Todays FX Rate as a formula numeric field with a formula of 1.  Make a relationship to the FX Rate Today table based on that field.

Then do a summary maximum of the [Record ID#] and call that [Record ID# of most recent FX record].  look that up down to the FX Rates Table.

Then make a summary field of the Maximum (or Minimum or Average) of the FX rate where the [Record ID#] equals the value in the field [Record ID# of most recent FX record]

Great, now that Todays FX Rate Table knows the current rate.

So now just make another Relationship for the Todays FX Rate Table to all your Purchases based on the same technique - a field on the child side of the Relationship with a formula value of 1 and look up the Today's FX Rate.