I need a field in one table which shows the most recent record from a field in a related table.
- The two tables are "Lots List" and "Germ test" (we are talking about seed lots and germination tests here).
- germ test is a line item table
- Each seed lot may have multiple germ tests over its life time
- The tables are linked by "lot #"
- the fields in 'germ test' are: 'lot # FK', 'date', 'result', and 'next test' (date + 6 months)
- In the lots list i want to be able to see the most recent germ test result and the next germ date but when i make a field displaying 'result' in Lots list it shows the result from the first record created rather than the most recent
- is there a easy way to change this?