Preliminary note: on MS SQL Server you can use:
SELECT COUNT(DISTINCT <FieldName>) FROM ...
, but on MS Access database such as statement will cause an error. ;(
So, you can choose one of the way:
1) subquery
2) joined table
This should work as expected:
SELECT tmp.[Player Id], SUM(tmp.[MatchPlayed) AS MatchPlayed, SUM(t2.Goal) AS PointsReached
FROM (SELECT DISTINCT [Player Id], COUNT([Match Id]) As MatchPlayed
FROM [Match Details]
GROUP BY [Player Id]
) AS tmp INNER JOIN [Match Details] AS t2 ON tmp.[Player Id] = t2.[Player Id]
GROUP BY tmp.[Player Id];
For further details, please see:
Access Subquery Techniques[
^]
Tips and Techniques for Queries in Access 2007[
^]