Populating a field in a related table based on a condition
I'm hoping there's a relatively easy way to do this, but I haven't been able to so far.
I'm creating a database of training courses. One requirement is that we keep a history of price changes for the courses. I have a table for the Courses, and a related (1-to-many) table of Course List Prices. The Course List Prices table contains the following fields: Course ID, List Price and Date.
How can I retrieve the current list price (the list price with the latest date) to populate a field in another table?
One idea I had is to use the "GetNthRecord" function, but I would need a way to ensure that the current price is the first one. Is there a way to sort the records by the Date field when doing a calculation?