Into which table are looking up the rate? TimeSheets?
If so, I'd set up this relationship:
Timesheets::Customer# = Rate::Customer#
Double click the relationship line and click the sort button on the Rate side of the relationship. Specify that the relatd Rate records by sorted by Date in descending order.
Then the most recently dated Rate for the specified customer# will be returned by this relationship.
I do not have a customer field in the timesheets table. I was trying to do this without having to add that field to the timesheets table. It is easy enough to populate the field as the record is created.
But in what table are you attempting to look up the rate?
Is the same rate always used for the same job?