Without making major changes to your data model, you need a relationship between your Table (not layout) A and Table B that links the records by department.
I'll call Table A "WorkRecords" and Table B "Departments". If they are linked like this:
Departments::__pkDepartmentID = WorkRecords::_fkDepartmentID
Then in any record in WorkRecords on your WorkRecords layout, you can select a department in the _fkDepartmentID field followed by selecting a Skill level in a SkillLevel field using your value list.
You can then set up a Number field, ChargeRate, in WorkRecords that auto-enter's the needed value using a Case function:
Case ( SkillLevel = "Trainee" ; Departments::Trainee Charge Rate;
SkillLevel = "Semi Skilled" ; Departments::Semi Skilled Charge Rate;
SkillLevel = "Skilled" ; Departments::Skilled Charge Rate ;
A more drastic approach would be to split up your 8 Department Entries into 24 records, one for each unique combination of Department and Skill Level. There is no immediate need to do this, but it might make some aspects of managing this (such as making it easier to add new skill levels and rates) data easier. With the data structured in that format, you'd use both the Department ID and the skill level to match to the record with the correct date and you would no longer need the case function shown above as the relationship would then only match to the single value needed.
Thank you for your answer but it doesn't seem to be working 100%. Maybe I'm doing something wrong but it's changing the rate on the Employee form as I change the skill level but it isn't changing the rate based on the department they are in.
My Table 'A' is called Employees and Table 'B' is Departments.
I have this set as one [Departments: :__DepartmentIDpk] to many [Employees: :_DepartmentIDfk] as one department can have many employees.
Even with this set as such and the case calculation entered into the [Employees: :Charge Rate 1] field, it isn't working.
Please let me know if you have any other suggestions.
My suggestion should work for what you describe. Please describe the details of what you did in your attempt to implement this in more detail.
My bad, I just checked the table layout and noticed the foreign key field hadn't been populated because my department dropdown was related to a field called department as opposed to the _DepartmentID_fk.
Thanks so much, it works fantastically. :)