Discussions

 View Only

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

  • 1.  Populate table with field from another table based on two fields matching

    Posted 11-26-2021 07:57
    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
    ------------------------------