IF Statement to lookup specific values in a field and also a count with multiple criteria

Hi Access pro heads, This site has helped me immensely improve my Excel but now that I have been driven to develop an Access database, which i have never ever used, I am struggling with getting to grips with the querying and expressions despite the tutorial courses.

I have uploaded an employee database that logs their tasks. The tasks are ranked in difficulty by Colour and Score.

I have an employee details main form of all employee records which has a subform showing all the tasks for each employee record selected.

I need help building an expression to auto populate 3 field boxes on the employee records form which are: Last Supervised, Task Colour and Total Score.

Q1. Last Supervised: I need this to look up the [Supervised] field in Task Data for any active (viewed) employee, find the latest 'Yes' in that field and return and display the Supervised Date for that latest record.

Q2. Task Colour: If field [Task Colour] in Task Data for any active (viewed) employee, contains 'Red' and [Status] is Doing then Display 'RED' in task colour, else if 'Yellow' and [Status] is Doing then Display 'Yellow' else Display 'Blue'.

Q3. Total Score: Total the field [Score] in Task Data for any active (viewed) employee for all records that are Doing status and display this.

I really hope you could help me with this a I think I'll have a good basis to progress from here...

Thanks in advance...