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

This too can be done with a sub-form.
The sub form will have those 3 fields, LAST SUPERVISED, COLOUR, SCORE.

Make a query , qsMaxTask1Person, that pulls the MAX taskID from Task table, and from that 1 persons ID

SELECT Max([Task Data].[Task ID]) AS [MaxOfTask ID], [Task Data].Supervised, [Task Data].[Supervised Date], [Task Data].Score
FROM [Task Data]
GROUP BY [Task Data].[Employee ID], [Task Data].Supervised, [Task Data].[Supervised Date], [Task Data].Score
HAVING ((([Task Data].[Employee ID])=[forms]![EmployeeRecords]![ID]) AND (([Task Data].Supervised)="yes"));


Then attach this query as the form's datasource. Link them in the subform on ID.

Originally Posted by ranman256

This too can be done with a sub-form.
The sub form will have those 3 fields, LAST SUPERVISED, COLOUR, SCORE.

Make a query , qsMaxTask1Person, that pulls the MAX taskID from Task table, and from that 1 persons ID

SELECT Max([Task Data].[Task ID]) AS [MaxOfTask ID], [Task Data].Supervised, [Task Data].[Supervised Date], [Task Data].Score
FROM [Task Data]
GROUP BY [Task Data].[Employee ID], [Task Data].Supervised, [Task Data].[Supervised Date], [Task Data].Score
HAVING ((([Task Data].[Employee ID])=[forms]![EmployeeRecords]![ID]) AND (([Task Data].Supervised)="yes"));


Then attach this query as the form's datasource. Link them in the subform on ID.

Hi ranman256, Thanks you very much for taking the time to assist me, with the risk of coming across as ungrateful, I would really benefit from a working example of the database I posted simply because as good as your explanation my be to access users, I am really spanking new to it and despite quickly grasping it's structuring logic and terminology, I am still learning the querying, sql language and trying to get to grips with it and I learn better by taking a working example and together with your explanation analysing what's happening so I can replicate that in future... it's how I became better at excel... and hopefully access.

So could I please kindly ask you to try post a working copy of the database I uploaded so I work through it.

Thanking you in advance.