Lookup next - related files
I need to look up a billing rate from a related file. My database has four tables, customer, job, timesheets and rates. The job and rate tables are related to the customer file by a customer#. The timesheets and job tables are related by a contract number. The rate file has three fields; customer#, date, and rate. Different customers have different rates and the rates change at different times for different customers. Each time there is a rate change, a new record is written for each field.
I need to look up the most current rate each time a new record is written to the timesheet file. I am trying to get the rate by the following path
timesheets -> job -> customer -> rate
Any suggestions would be greatly appreciated.