Populate table with field from another table based on two fields matching

Hi there

I have a table of financial values called [Role-based Limits] which are unique based on two fields - [role]&[region]=[value]
A user can add more than one [value] based on [role] and [region] by adding a new effective date. So, a unique value would be [effective date]&[role]&[region]

In a second table called [Authority Limits] I have employee data. There is a relationship (1 authority limit to many Role-based Limits)

In [Authority Limits] table I also have [role] and [region] fields. What I want to do, is when role and region are populated in [Authority Limits], the latest [effective date] and associated [value] fields are populated from the [Role-based Limits] table.

I do have a look-up field for the [value] in the [Authority Limits] table, but this is just a dropdown of all of them. 

How do I do this??

------------------------------
Jemma
Data Analysis & Visualisation
United Kingdom
------------------------------