Subquery with sum

[herbertioz]

I need some help here...

I have 1 one to many relationship with Deltagere (Athletes) and Konkurranse (Competitions). I want to present the sum of the weights (TotalVekt) for each Team (Lagnavn) and the atheletes with their weight in the same query.

The sql under shows the teams and their weights, but not with athletes of the team together with their individual weight. I think the solution is a subquery...

I have this sql:

SELECT Deltagere.Lagnavn, Sum(Deltagere.Vekt) AS ["TotalVekt"]
FROM Konkurranse INNER JOIN Deltagere ON Konkurranse.ID=Deltagere.KonkurranseID
WHERE (((Konkurranse.Konkurransenavn)="LakeMichigan"))
GROUP BY Deltagere.Lagnavn
ORDER BY Sum(Deltagere.Vekt) DESC;