Access SQL JOIN with subquery

Can someone tell me how I can convert this query to work in MS Access?

SELECT Shooters.idShooters, Shooters.firstname, Shooters.lastname, JoinedContingent.Count, JoinedShots.Count 
FROM Shooters 
INNER JOIN 
          (SELECT Shooters.idShooters, Count(Contingent.idContingent) AS Count
           FROM Shooters LEFT JOIN Contingent ON Shooters.idShooters = Contingent.fidShooter
           GROUP BY Shooters.idShooters) 
AS JoinedContingent ON JoinedContingent.idShooters = Shooters.idShooters 
INNER JOIN 
          (SELECT Shooters.idShooters, Count(Shots.idShots) AS Count
           FROM Shooters LEFT JOIN Shots ON Shooters.idShooters = Shots.fidShooters
           GROUP BY Shooters.idShooters) 
AS JoinedShots ON JoinedShots.idShooters = Shooters.idShooters;

---

Background information:

I would like to count the foreign key occurrences for each Shooter in the table Contingent and Shots. The result should look like this:

  idShooters  |  firstname  |  lastname  |  Count  | Count
____________________________________________________________
      1            John          Doe          0       10
      2            Jane          Doe          1       20
      .
      .
      .

Answer

I think this is what you want:

SELECT s.idShooters, s.firstname, s.lastname,
        NZ(c.Count, 0), NZ(sh.Count, 0)
FROM (Shooters as s LEFT JOIN 
      (SELECT c.fidShooter, Count(*) AS Count
       FROM Contingent as c
       GROUP BY c.fidShooter
      ) as c
      ON s.idShooters = c.fidShooter
     ) LEFT JOIN
     (SELECT sh.fidShooters, Count(*) AS Count
      FROM Shots as sh
      GROUP BY sh.fidShooters 
     ) as sh
     ON s.idShooters = sh.fidShooters;

Note that I moved the outer join to the outer query. Actually, no joins are needed in the subqueries, so don’t bother.