Access SQL: How to retrieve sums of multiple values where user IDs are assigned to multiple positions

I’m working on an Access database for assigning tasks to personnel and tracking task status and workload. A single user ID can be assigned to one of many fields associated with a particular task. In this case, the Task table has fields for “TechReviewerID” “DesignerID” “TechReviewerWorkload” and “DesignerWorkload.”

I want one query to return one row for each person, with two summary columns totaling all of the workload assigned to them. So if I’m ID1, I want column 3 to return the sum of “TechReviewerWorkload” in all tasks where “TechReviewerID = 1” and column 4 to return the sum of “DesignerWorkload” in all tasks where “DesignerID = 1.”

I have successfully written two separate queries that accomplish this:

    SELECT MESPersonnel.MESID, MESPersonnel.PersonnelName, 
    IIF(SUM(DesignerTask.DesignerWorkload) IS NULL, 0, SUM(DesignerTask.DesignerWorkload)) AS 
    TotalDesignerWorkload

    FROM 
    (MESPersonnel LEFT OUTER JOIN Task AS DesignerTask ON (MESPersonnel.MESID = 
    DesignerTask.DesignerID 
    AND DesignerTask.DueDate < CDATE('2020-07-30') AND DesignerTask.DueDate > CDATE ('2020-05-01')))
        
        WHERE MESPersonnel.PositionID = 1
            GROUP BY MESPersonnel.MESID, MESPersonnel.PersonnelName;

This query gives the following table:

MESID  PersonnelName  TotalDesignerWorkload
  1    John Doe                40
  2    Dohn Joe                20

I can create a near-identical query by replacing all instances of “designer” terms with “tech reviewer” terms.

What I’m looking for is a table like:

MESID  PersonnelName  TotalDesignerWorkload  TotalReviewerWorkload  
  1      John Doe              40                     10            
  2      Dohn Joe              20                     20            

My attempts to combine these two via multiple outer joins resulted in wildly inaccurate sums. I know how to solve that for items on different tables, but I’m not sure how to resolve it when I’m using two items from the same table. Is there some kind of conditional sum I can use in my query that Access supports?

EDIT: Sample Raw Data

    Task Table

    TaskID  DesignerID  TechReviewerID  DesignerWorkload  TechReviewerWorkload  DueDate
      1         1              2                40                20             06-20-2020
      2         2              1                20                10             06-20-2020


    MESPersonnel Table

    MESID  PersonnelName
      1       John Doe
      2       Dohn Joe

Answer

Consider:

Query1: TaskUNION
rearranges data to a normalized structure

SELECT TaskID, DesignerID AS UID, PersonnelName, DesignerWorkload AS Data, DueDate, "Design" AS Cat FROM MESPersonnel 
    INNER JOIN Task ON MESPersonnel.MESID = Task.DesignerID
UNION SELECT TaskID, TechReviewerID, PersonnelName, TechReviewerWorkload, DueDate, "Tech" FROM MESPersonnel 
    INNER JOIN Task ON MESPersonnel.MESID = Task.TechReviewerID;

Query2:

TRANSFORM Sum(Data) AS SumData
SELECT UID, PersonnelName
FROM TaskUNION
WHERE DueDate BETWEEN #5/1/2020# AND #7/31/2020#
GROUP BY UID, PersonnelName
PIVOT Cat;

An alternative would involve 2 simple, filtered aggregate queries on Task table then join those 2 queries to MESPersonnel. Here as all-in-one statement:

SELECT MESID, PersonnelName, SumOfDesignerWorkload, SumOfTechReviewerWorkload
FROM (
   SELECT DesignerID, Sum(DesignerWorkload) AS SumOfDesignerWorkload
   FROM Task WHERE DueDate BETWEEN #5/1/2020# AND #7/31/2020# GROUP BY DesignerID) AS SumDesi 
RIGHT JOIN ((
   SELECT TechReviewerID, Sum(TechReviewerWorkload) AS SumOfTechReviewerWorkload
   FROM Task WHERE DueDate BETWEEN #5/1/2020# AND #7/31/2020# GROUP BY TechReviewerID) AS SumTech 
RIGHT JOIN MESPersonnel ON SumTech.TechReviewerID = MESPersonnel.MESID) 
ON SumDesi.DesignerID = MESPersonnel.MESID;