How to get a value from one record of related records in a calculated field?
I have a few questions how to get a specific value from one record in a set of related records I want to use in a calculation in another table.
Either it is really simple and I am thinking far too complicated or I have set up my database in a way it complicates this issue.
Set up of the database:
Departments >--------- Professions -------------< Growth rates
People of only one profession work at a department. Several departments (of different companies) may inhabit people with the same profession.
A profession has a certain growth rate (within a region).
Regions -----< Companies -------< Departments
A department belongs to a company. Companies are grouped in regions.
Regions TO ---------< Growth rates
The growth rate table inhabits both regional growth rates (for each profession) and growth rates that apply nation wide (for each profession). The distinction between these two growth rates is made in a separate field.
1. What I want in the Department table is a calculated field that presents the region growth rate for a specific profession within a company. Which would give the same growth rate for all departments (with the same profession) of companies in a certain region, but which will vary among a certain profession between various regions.
2. And in another field I would like to have the growth rates that apply nation wide. These would only vary between different professions and not between regions.
Any help would be so gladly appreciated.