Trying out this query in MS Access - Please let me who where i am wrong

Question

The aim is to join the same table and retreive two different columns using where clause

SELECT a.analyst_assigned
     , count(a.case_id) AS Pending
     , count(b.case_id) AS Completed
     , sum(b.total_errors) AS Errors
  FROM cases_main as a
 WHERE a.team_qc1_qa2 = "qa" 
   and a.q_status in ("Not Started","Under Review","Follow-up") 
   and a.p_date_worked between #03/08/2021# and #03/14/2021#

    INNER JOIN cases_main as b

    ON a.analyst_assigned = b.analyst_assigned

 WHERE b.team_qc1_qa2 = "qa" 
   and b.q_status in ("Completed: No Error","Completed: Feedback") 
   and b.p_date_worked between #03/08/2021# and #03/14/2021#

 GROUP 
    BY b.analyst_assigned;

Answer 1

Try this simpler approach:

SELECT a.analyst_assigned
    , abs(sum(q_status Like "Completed*")) AS Pending
    , abs(sum(q_status Not Like "Completed*")) AS Completed
    , sum(b.total_errors) AS Errors
FROM cases_main as a
WHERE a.team_qc1_qa2 = "qa" 
    and a.p_date_worked between #03/08/2021# and #03/14/2021#
GROUP BY a.analyst_assigned