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
------------------------------
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
------------------------------